Join (Superseded) - Data360_Analyze - 3 - 3.12

Data360 Analyze Server Help

Product type
Software
Portfolio
Verify
Product family
Data360
Product
Data360 Analyze
Version
3.12
Language
English
Product name
Data360 Analyze
Title
Data360 Analyze Server Help
Copyright
2023
First publish date
2016

Matches records from two data sources to create an enriched data set with values from both inputs whenever the specified field is found to exist in both sources.

Note: This node has been superseded by the Join node which provides similar functionality, but the underlying code is Python rather than Data360 Analyze Script. The Join (Superseded) node is provided for backwards compatibility, but where possible it is recommended that you use the new Join node.

In total, there are six variations of Join node that have been superseded by the new Join node. You can now perform all join variations within a single node, see Join.

This node performs a relational join on the data from both input pins, using the criteria defined in LeftInputKey and RightInputKey to determine matches, left orphans, and right orphans. The inputs must be sorted by their respective keys before entering this node.

Matches are filtered at two different phases in this node. First, only matches whose type match the JoinType are evaluated against the Script in the Script property. In other words, if the JoinType is set to "ir" for a right inner join, then any left orphans are discarded before reaching the Script property. All matches whose type matches the JoinType are then evaluated against the Script in the Script property. In this property, you can define additional filtering criteria, if desired, in a where clause on the emit statement.

In addition to the standard Script functions and variables, the Script property has four additional Script variables available to it. These variables classify the records ready to be output as either left orphans, right orphans, or matches.

  • join.type - This variable will be either "l", "i", or "r", depending upon whether a left orphan, right orphan, or match was found. If a left orphan was found, then this variable is set to "l". Conversely, for a right orphan, this variable is set to "r". Finally, for a match, this variable is set to "i". Note that this variable provides more detail than the JoinType property. JoinType simply defines all relationships that will be evaluated against the Script property. join.type specifies exactly which relationship was discovered for the given record(s).
  • join.rightOrphan - Set to true when a right orphan is found. This is only true when join.type is "r".
  • join.leftOrphan - Set to true when a left orphan is found. This is only true when join.type is "l".
  • join.match - Set to true when a match is found. This is only true when join.type is "i".

Properties

JoinType

Specify the type of join. Options are l (left), i (inner), r (right), or a combination of these. For example, you could do a left inner join via "li".

LeftInputKey

Optionally specify a Script expression to be performed on each row of the first, or left, pin that determines the key on which to join to the right pin.

RightInputKey

Optionally specify a Script expression to be performed on each row of the second, or right, pin that determines the key on which to join to the left pin.

Script

Specify a Script that defines what to output under what conditions. The Script in this property is evaluated against the last two rows used in producing either a match or an orphan. In the case of an orphan, then the fields on the side that does not match are all NULL.

Usually, this property simply contains an emit statement that lists the fields you would like to output. However, this property could also contain a where clause that filters the outputs based on an additional set of criteria. Also, additional calculations can be made in this property that are included in the output. In sum, anything that can be done in a Filter can also be done in this Script property.

A value is required for this property.

JoinExpr

Deprecated. Optionally specify an expression controlling what to compare.

VerifyInputsSorted

Optionally specify whether this node will verify that the inputs are sorted according to the input key expressions.

Note that enabling this property can cause a slight performance decrease in certain circumstances. Specifically, if one input is significantly longer than the other, and no more matches can be made, then the node still has to process the rest of this longer table in order to verify that it is in order.

The default value is True.

Inputs and outputs

Inputs: left, right.

Outputs: out1, multiple optional.