Create Left Join in Pipeline
Introduction
A left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
From this document, you will learn how to create the Pipeline, which contains the left join, here the left join connects the multiple tables to make the Pipeline dataset.
Prerequisites
To Schedule a Pipeline, you need working Parameter Datasets(JDBC) and a list of tables and views.
Objective
How to Create Left Join in Pipeline.
Download Pipeline.zip file from the link given Pipeline.zip
Follow steps to create Left-join Pipeline in OPNBI:-
Login to the OPNBI application with valid user credentials.
Go to Master Data > Dataset section from the Hamburger menu.
Go to the Dataset section, Click on Create Pipeline from the bottom menu bar. As shown in the figure below:
Once you click, you can see a blank create pipeline window as in the below image:
Enter the Following detail in the Pipeline dialog box:
Name: Left Join
Source: OPNBI-mapping
Expand tables from the left side table view, As shown in the figure below:
Drag and Drop the OFFICE table from the table box and drag to the diagram box at the right side, and select all the columns from the Select Column dialog box, as shown in the figure below:
Click on Submit button, after dragging & drop the OFFICE table in the diagram box, look like the figure below:
Drag and drop the second table, take the CUSTOMERS table, and drop it in the diagram box.
After dropping the CUSTOMERS table, select all the columns from the Select Columns dialog box, as shown in the figure below:
Click on submit button, offices and customers table from the diagram box will look like the figure below:
After adding OFFICES and CUSTOMERS from the table list, Drag the COUNTRY column from Offices to 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 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 (select the Pipeline, which you are created)
The two tables will get load the left side box of Pipeline view, Now expends the OFFICES 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 on Cancel Button it will show the screen as follows:
Click on Save as
icon for saving the Analysis, it will show the screen as below:
Give the name of Analysis.
Name: Left Join Pipeline and Select the folder where you want to save Analysis, as shown in the figure below:
Click on Save As button to save Analysis.