Join - Data360_DQ+ - Latest

Data360 DQ+ Help

Product type
Software
Portfolio
Verify
Product family
Data360
Product
Data360 DQ+
Version
Latest
Language
English
Product name
Data360 DQ+
Title
Data360 DQ+ Help
Copyright
2024
First publish date
2016
ft:lastEdition
2024-07-09
ft:lastPublication
2024-07-09T15:09:58.774265

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:

Data Store 2 Data Store 1 Data Store 2 Data Store 1 Full Outer Right Outer Data Store 1 Data Store 2 Left Outer Data Store 1 Data Store 2 Inner

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.