Joining Multiple Data Sources

There are occasions where the desired data is not achieved or available using a single query and table. This is often the case with time series where you want to join a static data set to a time series database.

To join multiple tables, add the source tables in the Edit Data Table screen and join them using a common field or a join key. Furthermore, you can also perform a transform of a table for time series analysis, if required.

 

NOTE	·         Joining two data sources can be done using more than one left and right key columns is now supported. ·         It is no longer needed to modify the data types to text to join data sources.

 

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

Sample Table 1

Item	isodatetime	ask_price	ask_volume	bid_price	bid_volume
Price	2008/01/17 13:00:00	17.75	2	17.65	1
Rate	2008/01/17 13:00:01	17.70	2	17.64	1
Price	2008/01/17 13:00:00	17.74	1	17.61	1

Sample Table 2

TradeID	RatePrice	ISODateTime	trade_price	trade_volume	Side	AggressivePassiveDark
1	Price	2008/01/17 13:00:00	17.79	200	Buy	Aggressive
2	Rate	2008/01/17 13:00:02	17.65	100	Sell	Dark
3	Price	2008/01/17 13:00:04	17.72	100	Buy	Dark

Steps:

1.     On the Data Sources Settings pane, add a new data source by clicking the Add Data Source   button.

 

The list of available connectors is displayed on the Connectors pane.

 

2.     Select a data connector to browse the new data source.

When there are two or more data sources on the Data Sources area, the Join  icon is displayed.

 

Here is a sample with the bidoffertrade.xlsx – Price and bidoffertrade.xlsx – Trade data sources:

 

The Data Preview area displays the data of the highlighted or selected data source bidoffertrade.xlsx – Trade.

To display the other data source (bidoffertrade.xlsx – Price) on the Data Sources Preview area, click the data source.

3.     To join the data sources, click the Join  icon.

The icon changes to    and the  Join Settings pane displays.

 

4.     Select the join Type:

·         Left Outer Join

Keep all rows from the left table. When there are no matching values from the right table, empty values will be returned.

·         Right Outer Join

Keep all rows from the right table. When there are no matching values from the left table, empty values will be returned.

·         Inner Join

Select only rows from both tables for which the join keys match.

5.     Click .

 

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

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

8.     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 Inner Join, the joined table now displays seven rows based on the Item/RatePrice join keys of both tables.

 

 

9.     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.

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

11.   Again, select the join Type.

12.   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 Inner Join, the joined table now displays one row based on the Item/RatePrice and isodatetime/ISODateTime join keys of both tables.

 

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

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

 

NOTE

·         If there is an error in the join definition, the Join icon or Left/Right Column drop-down is marked with a red border. Consequently, the preview is not displayed.          For example, if the join keys have different data types, an error message is displayed:         “A panopticon.developer.tables.standalone.TextColumn column from the left table in the join is not compatible with a panopticon.developer.tables.standalone.NumericColumn column in the right.”