Create Left Join in Pipeline

Introduction

A left join in a pipeline on AIV allows you to combine data from multiple tables while ensuring that all records from the left table are retained, regardless of whether they have matching records in the right table. This ensures that no data is lost during the join operation, and unmatched records from the right table are filled with NULL values.

Prerequisites

The prerequisites for setting up an left join pipeline typically include :

1. Access to the AIV application with appropriate permissions.

2. Availability of datasets or tables to be joined.

3. Understanding of the common field or column on which the join operation will be performed.

4. Knowledge of the left join concept and its application in combining data from multiple sources.

5. Familiarity with the AIV interface for creating and configuring pipelines.

6. Working parameter datasets, particularly if dynamic filtering or conditions are involved.

7. Clear understanding of the desired output and how the joined data will be utilized downstream.

These prerequisites ensure a smooth setup and execution of the left join pipeline within the AIV environment.

Objective

Follow steps to create Inner-Join Pipeline in AIV :

1. Download Pipeline.zip File: Access the link provided to download the Pipeline.zip file to your local system. Download Pipeline.zip file from the link given. Pipeline.zip

2. Extract the ZIP File : Once the download is complete, extract the contents of the Pipeline.zip file to a folder on your computer.

3. Access AIV Platform : Log in to the AIV platform using your credentials.

Navigate to the Dataset section and click on “Create Pipeline” located in the bottom menu bar, as illustrated in the figure below.

After clicking, you will see a blank window for creating a pipeline, as depicted in the image below.

Enter the Following detail in the Pipeline dialog box:

Name: Left Join

Source: classicmodels 1.4 (select as per your requirement)

Expand tables from the left side table view, As shown in the figure below:

Drag and drop the “brij_offices” from the list of tables,datasets to the diagram box on the right side, then select all the columns of the “brij_offices”, as shown in the figure below.

Click on Submit button, after dragging & drop the brij_offices table in the diagram box, look like the figure below:

Drag and drop the brij_customers from the left table menu to the right diagram box.

After dropping the brij_customers table, select all the columns from the Select Columns dialog box, as shown in the figure below :

Click on submit button, brij_offices and brij_customers table from the diagram box will look like the figure below:

After adding brij_offices and brij_customers from the table list, Drag the country column from brij_offices to brij_customers, as shown in the figure below :

Now to change the Pipeline configuration from inner join to left join, click on the table join the line, The Pipeline configuration box will open, as shown in the figure below :

Click on Submit button to save the changes.

The Connection between the two tables will look as shown in the figure below:

Click on Submit to save the Pipeline.

The pipeline is run in the Adhoc Analysis.

Go to Documents > Adhoc Analysis section from the Hamburger menu. In Adhoc Analysis, click on the + icon to create a new Analysis, as shown in the figure below.

When you click on create Analysis then the Analysis Selection dialog box will open, as shown in the figure below.

Enter the following details in Analysis Section.

Dataset: left join brij (select the Pipeline, which you are created)

The two tables will get load the left side box of Pipeline view, Now expends the office table.

Drag and drop CITY column from OFFICES to Rows, Drag and drop STATE column from OFFICE and Put it in Column box, Drag and drop the CUSTOMER NUMBER column from Customers to Values box (Select sum in Type), as shown in the figure below :

Click on Preview Button it will show the output and after the preview click.it will show the screen as follows :

Click on Save as icon for saving the Analysis, it will show the screen as below:

Give left join brij the name of Analysis.

Select the folder where you want to save Analysis, as shown in the figure we are save into adhoc analysis pipeline folder below :