How To Use A Join Block
If you’re used to using spreadsheets, a join is similar to a Vlookup where you are adding data from one table (a reference table) to a new column in another table (a data table). The data is added based on a matching condition where the value of a given cell in the reference table is matched with the resulting row in the data table. The value from any column in the reference table can be brought in to the data table.
Analytics Canvas allows you to join together any tables that have been connected to your Canvas using one of 7 join conditions:
- Inner Join
- Left outer join
- Right outer join
- Full outer join
- Left not joined
- Right not joined
- Left and Right not joined
To join more than one table together, simply add additional join blocks and perform the join in multiple steps.
1. Add the data sources; you will need two for the Join block. Connect the outputs on the data sources to the inputs on the join block.
2. Select the Join block by clicking the center of it. In the lower right control window, you will see "Table 1" and "Table 2," representing the columns available in each data source.
Select one column from each table to create the join condition, then click "Add Join".
Note the following:
- More than one column can be used to form the primary key and create the join condition.
- The column names selected from each table do not need to be the same.
3. The columns used to form the join are listed under 'Join Columns'. To remove a join condition, click the delete icon next to it.
4. Under the 'Join Type' dropdown menu, you will find all the Join types available, each with its own Venn diagram to explain the result better.
5. Here are the details for each Join type available.
6. After you make the selection in the dropdown menu, you can click 'Refresh' to visualize the result of the Join operation.
7. After the refresh is complete, you will see the results of the join. In the case of an inner join, the diagram shows the number of rows from each table that did and did not meet the join condition.
8. On the main Canvas, click on the top right output node of the join block to see and use the result of the join, regardless of which join type was used.
9. The other two output nodes are the resulting rows from Table1 and Table 2 that did not Join.