Join

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

Join

Description

Merge two inputs based on common (key) columns, e.g. on an email address or id column present in both inputs. The bottom dataset (by vertical position) is joined to the right of the top dataset.

 

Example

Join two datasets side-by-side using the 'ID' columns:

 

how to join 2 datasets uisng a unique id example

 

Inputs

Two.

 

Options

The output depends on the vertical (Y-axis) position of the inputs.

Select Top key column for the column you want to match in the top input dataset.

Select include top non-matching rows if you want to include in the output any rows in the top input with no matching value in the bottom input.

Select Bottom key column for the column you want to match in the bottom input dataset.

Select include bottom non-matching rows if you want to include in the output any rows in the bottom input with no matching value in the top input.

 

include top non-matching rows checked

include bottom non-matching rows checked

Also known as:

No

No

Inner join

No

Yes

Right outer join

Yes

No

Left outer join

Yes

Yes

Full outer join

 

Notes

Join merges two datasets side-by-side (horizontally). To merge datasets one on top of the other (vertically) use Stack.

If there are 10k rows or less in both datasets, Easy Data Transform will try to guess sensible default values for Top key column and Bottom key column based on column header names and contents.

All values are treated as text and comparisons are case and whitespace sensitive. You can use Case to change the case and Whitespace to remove whitespace before the intersect.

If a key value occurs M times in the first dataset and N times in the second dataset, you will get M x N rows with this key value. You can use Unique to remove rows with duplicate key values.

Use Concat Cols to join several columns together (e.g. 'first name' and 'last name' columns) to form a key column.

Use Row Num to create a unique key column.

Use Sort to sort the results.

Use the Cross transform for cross joins.

Messages are shown in the Warnings tab for keys that occur more than once in either dataset (duplicates).

Messages are shown in the Info tab for keys that only occur in 1 of the 2 datasets (misses).

Cascade multiple joins to join more than 2 datasets.

 

cascading join example

 

See also

Video: How to join Excel files

Cross

Lookup

Interpolate

Merge datasets