The Join node allows you to combine fields from two separate data stores to create a new sheet made of rows where related fields are matched.
To perform a successful Join, the two chosen fields must be related by a common identity field.
The Analysis Designer features four join types:
Inner
Only creates a row if the left field value has a matching field value on the right.
Left Outer
Creates rows for every left field value and joins any matching right field values. Does not include right field values without a matching left field value.
Right Outer
Creates rows for every right field value and joins any matching left field values. Does not include left field values without a matching right field value.
Full Outer
Creates rows for all left and right field values, regardless of whether there is a match between left and right.
Example
The first data set (left) contains order transactions:
| AccountIDint | Datedate | Itemstring |
|---|---|---|
| 1000101 | 2016-01-01 | Tea |
| 1000102 | 2016-01-01 | Coffee |
| 1000103 | 2016-01-01 | Scone |
| 1000104 | 2016-01-02 | Water |
| 1000105 | 2016-01-12 | Tea |
The second data set (right) contains customer information:
| AccountIDint | Namestring | Addressstring |
|---|---|---|
| 1000101 | MARCIA SIDONY | "10 Rose Lane, Springfield" |
| 1000102 | DAVE THURSTAN | "73 Gorham Street, Boston" |
| 1000103 | ALEXANDRA GILLIAN | "7 James Street, Florida" |
| 1000104 | HALEY JANNA | "78 Summer Way, Denver" |
You want to join these two data sets to see if there are any transactions that are not associated with a customer, and any customers who have not made any transactions. The input field AccountID is common to both data sets, so you can use this field to join them (note that even if the names of the input fields did not match, if the contents of the fields are similar they can still be used to join).
The following table shows the combined data:
| AccountID | Date | Item | Name | Address |
|---|---|---|---|---|
| 1000101 | 2016-01-01 | Tea | MARCIA SIDONY | 10 Rose Lane, Springfield |
| 1000102 | 2016-01-01 | Coffee | DAVE THURSTAN | 73 Gorham Street, Boston |
| 1000103 | 2016-01-01 | Scone | ALEXANDRA GILLIAN | 7 James Street, Florida |
| 1000104 | 2016-01-02 | Water | HALEY JANNA | 78 Summer Way, Denver |
Properties
Display Name
Specify the name of the node that is displayed on the Analysis Designer canvas.
The default value is Join.
Join Type
Select a join type. Choose from:
- Inner
- Left outer
- Right outer
- Full outer
The default value is Left outer.
Select Right Source fits in memory and has Unique values of Join Fields to optimize the join, if the right source is small enough to fit in memory. You can only select this option if the right source values are unique.
Join Fields
Click the Add Field button to specify the left and right input fields on which you want to base the join, then click Update. The selected fields must be related to perform a successful join.
Output Fields
Select the fields that you want to include in the output.