This deprecated node combines left, right and inner joins into a single node.
Joins two data sources using a comparison expression to determine if the specified data is in the left input only (first input), in the right input only (second input), or in both inputs. The X-Ref node is a powerful analysis tool that gives you full visibility of matches and non-matches. The results of each join are segregated and output to three different pins, as follows:
- First output pin (left orphans): Data that appears in the first (left) data source but not in the second (right) data source.
- Second output pin (matches): Data that appears in both data sources.
- Third output pin (right orphans): Data that appears in the second (right) data source but not in the first (left) data source.
When using the X-Ref node:
- Firstly, the inputs must be sorted (as specified by the join expression), or the SortLeftInput and SortRightInput should be set to True.
- In the LeftInputKey and RightInputKey fields, enter the input field names that you want to join on.
- If you want to further filter the output, enter Script code in the Script property. See the Transform (Deprecated) or Split (Deprecated) node help for examples of Script code that you can use in this property.
Example
You have the following input data. The first data set contains order transactions, for example:
| AccountIDint | Datedate | Itemstring |
| 1000101 | 2016-01-01 | Tea |
| 1000102 | 2016-01-01 | Coffee |
| 1000103 | 2016-01-01 | Scone |
| 1000104 | 2016-01-02 | Water |
The second data set contains customer information, for example:
| 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).
In the LeftInputKey and RightInputKey properties, enter:
AccountID
Ensure that the SortLeftInput and SortRightInput properties are set to True.
When you run the X-Ref node, the first output pin will list any data that only appears in the first data set, in the case of the above example, it would contain no data.
The second output pin will list all matches, as follows:
| 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 |
The third output pin will list any data that only appears in the second data set, in the case of the above example, it would contain no data. You can then decide to continue your analysis by joining the "matches" data to another node or you can investigate the left or right orphans.
Properties
SortLeftInput
Optionally specify whether the first input is sorted based on the value specified in the LeftInputKey property.
The default value is False.
SortRightInput
Optionally specify whether the second input is sorted based on the value specified in the RightInputKey property.
The default value is False.
LeftInputKey
Specify input fields from the first (left) data set on which you want to base the join.
A value is required for this property.
RightInputKey
Optionally specify an expression relative to the second (right) input, used to determine the key on which to join.
The default value is the same value that is specified in the LeftInputKey property.
Script
Optionally specify Script to further filter the output.
Inputs and outputs
Inputs: left, right.
Outputs: left orphans, matches, right orphans.