Joining Multiple Data Tables in the Joined Data Table Editor

 

In this section, we will discuss how to join the following data tables using two common fields.

Sample Data Table 1 (e.g., BidOfferTrade – Price)

 

Sample Data Table 2 (e.g., BidOfferTrade – Trade)

 

Steps:

1.      Create a new joined data table by doing one of the following:

·        Click   on the Data Library page, or

·        Right-click on a folder or subfolder then select New Data Table in the context menu.

 

The New Data Table dialog displays.

 

2.      Enter the name of the joined data table then click .

The Joined Data Table Editor displays.

 

3.      On the Join Settings pane, click . The Add Data Table dialog displays.

4.      Click the data tables that will be joined. Selected data tables are now highlighted.

 

5.      Click . The selected data tables are now added under the Join Settings pane.

 

6.      To join the data tables, click the Join  icon.

The icon changes to  and the Join Settings pane displays the join types you can use.

 

7.      Select the join Type:

Join Type	Description
Left Outer Join	Keeps all rows from the left table. When there are no matching values from the right table, empty values will be returned.
Right Outer Join	Keeps all rows from the right table. When there are no matching values from the left table, empty values will be returned.
Full Outer Join	Returns all rows from both tables, whether they have a matching row or not.
Inner Join	Selects only rows from both tables for which the join keys match.
Cross Join	Returns the Cartesian product of rows from tables in the join.

 

8.      Click .

The Left Column and Right Column drop-down lists are displayed.

 

9.      Select the unique ID from the Left Column data table from the drop-down list that will be used to match the unique ID from the Right Column data table (e.g., Item).

10.      Select the unique ID from the Right data table from the drop-down list (e.g., RatePrice).

 

11.      Click  then click  to expand the Data Preview pane.

The selected join type is displayed in the Join definition box and the data table of the joined data sources is loaded on the Data Sources Preview area.

·        For the Left Outer Join, the joined table now displays seven rows based on the Item join key of the left table.

 

·        For the Right Outer Join, the joined table now displays seven rows based on the RatePrice join key of the right table.

 

·        For the Full Outer Join, the joined table now displays all rows that are matching or not matching based on the Item/RatePrice join keys of both tables.

 

·        For the Inner Join, the joined table now displays seven rows based on the Item/RatePrice join keys of both tables.

 

·        For the Cross Join, the joined table now displays twelve rows based on the combination of each row from the first table with each row from the second table.

Note that Join Keys definition is not available.

 

12.      Now, let us add new left and right join keys. Click   on the Join Settings pane.

A new Left Column and Right Column entry displays.

 

13.      Select the left and right join keys (e.g., isodatetime and ISODateTime)

14.      Again, select the join Type.

15.   Click .

The selected join type is displayed in the Join definition box and the data table of the joined data sources is loaded on the Data Sources Preview area.

·        For the Left Outer Join, the joined table now displays three rows based on the Item and isodatetime join keys of the left table.

All of the rows from the left table are kept. Note that for the rows with no matching values from the right table, empty values are returned.

 

·        For the Right Other Join, the joined table now displays four rows based on the RatePrice and ISODateTime join keys of the right table.

All of the rows from the right table are kept. Note that for the rows with no matching values from the left table, empty values are returned.

 

·        For the Full Outer Join, the joined table now displays six rows from both tables. The first row is based on the Item/RatePrice and isodatetime/ISODateTime join keys of both tables while the next five rows are those that did not match the join keys.

 

·        For the Inner Join, the joined table now displays one row based on the Item/RatePrice and isodatetime/ISODateTime join keys of both tables.

 

·        For the Cross Join, the joined table now displays twelve rows based on the combination of each row from the first table with each row from the second table.

Note that Join Keys definition is not available.

 

16.   To delete left and right join keys in the Join Settings pane, click .

17.   Click  to save the join. Once saved, a notification message displays.