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.

 

Example

Lookup category name in a second dataset using 'Category ID':

 

how to lookup between files example

 

Inputs

Two.

 

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.

Click Explore Keys... to compare key values in the 2 datasets.

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

Select Bottom key 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 unique matches.

Set If no match to Use not match value or Leave unchanged depending on what you want to do for values in Top lookup column that do not exist in Bottom lookup column.

Set No match value to the value you want to use for values in Top lookup column that do not exist in Bottom lookup column when If no match is set to Use no match value.

Check case sensitive to use case sensitive matching for keys.

 

Notes

Easy Data Transform will try to guess sensible default values for Top lookup column, Bottom lookup column and Bottom value column.

Bottom values used is only important if there are duplicate values in the 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 whitespace sensitive. You can use Whitespace to remove whitespace before the lookup.

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

The new column is added at the right end. You can change the column order with Reorder Cols and the column name with Rename Cols.

Warnings are shown in the Warnings tab for:

oValues that cannot be found in the bottom dataset (misses).

oValues that occur more than once in the bottom dataset (duplicates), if Bottom values used is First.

 

See also

If

Interpolate

Join