Lookup

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

Lookup

Description

Looks up the values of a column in the top input dataset in the bottom input dataset and puts the result in a new column.

 

Inputs

Two.

 

Example

If you have one dataset with category IDs and another dataset with category IDs and category names, you can create a new category name column in the first dataset by looking up the category ID in the second dataset.

 

First dataset:

lookup example

 

Second dataset:

lookup example

 

Lookup transform:

lookup example

 

lookup transform

Result:

lookup example

 

Options

Place the dataset you want to modify as the top input and the dataset you want to lookup values from as the bottom input.

Select Top lookup column for the column whose values you wish to lookup.

Select Bottom lookup column for the column that matches the lookup in the bottom dataset.

Select Bottom value column for the column that contains the values.

Set Bottom values used to First if you want use the first match in Bottom lookup column and All if you want to use all matches.

Set Value if not found to the value you want to set for values in Top lookup column that do not exist in Bottom lookup column.

 

Notes

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.

Bottom values used is only important if there are duplicates in Bottom lookup column.

If the first input has a header, this will be used for the output.

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

If you want to lookup values in multiple columns, use Concat Cols to join several columns together to form new columns.

 

See also

If

Join