Validates whether two data sets are the same by comparing the key fields, and then for the records with matching key fields, performing a field-by-field comparison of all other fields.
The Full Merge 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 results of each join are segregated and output to four different pins. The first and fourth output pins are the same as the Merge node, but the second and third output pins are an extension to this node:
- 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 (full matches) - Data that appears in both data sources where all fields match.
- Third output pin (partial matches) - Data that appears in both data sources where the specified field matches, but some other fields do not match. This output contains one row per unmatched field within the source data.
- Fourth output pin (right orphans) - Data that appears in the second (right) data source but not in the first (left) data source.
By default, if there are any additional fields in either data set which are not matched, the node will error. You can override this behavior by setting the AllowPartialOverlap property to True to force the node to only compare a sub-set of fields where the field names match and to ignore the additional non-matching fields.
The configuration of the Full Merge node is as follows:
- Firstly, the inputs must be sorted, or the SortLeftInput and SortRightInput should be set to True.
- In the LeftInputKey property (and optionally also in the RightInputKey property), enter the input field names that you want to join on.
- To control the behavior of the node when comparing data sources that have slight differences, you can change the setting of the optional properties AllowTypeConversion and AllowPartialOverlap.
Properties
LeftInputKey
Specify an expression relative to the first (left) input, used to determine the key on which to join. This can simply be the name of a field. 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.
AllowTypeConversion
Optionally specify whether fields on the two inputs that have the same field name but are of different data types are to be compared as unicode strings. This does not apply to key fields. The default value is False.
AllowPartialOverlap
Optionally specify whether to prevent the node failing when a field is found in the first input that does not appear in the second input. In this case, these fields will be ignored for the comparison. The same applies to such unknown fields on the second input. The default value is False.
SortLeftInput
Optionally specify whether the first input will be sorted on the LeftInputKey. The default value is False.
SortRightInput
Optionally specify whether the second input will be sorted on the RightInputKey. The default value is False.
Inputs and outputs
Inputs: left, right.
Outputs: left orphans, full matches, partial matches, right orphans.