Lookup
The Lookup transform replaces the values of one or more key columns with the values you choose in another table.
This can be used to replace IDs with more descriptive text values, for example.
1. Input
The Lookup transform requires two input transforms.
For example, the main input could be the following data, which includes a DepartmentID:
With the following department details used as a lookup input:
2. Configure
The Lookup configuration dialog lists the columns from the two input tables, and allows you to choose the replacement values.
The lookup requires you to designate one or more columns as keys in each table. Rows from the lookup table with matching key values will be used to replace the key values in the output.
If a relationship between the two tables was already defined, the key elements may already have been set. This will be indicated by a key icon shown next to one or more items in each list, like in the figure above. If you want to remove an existing key binding for your lookup, click the Delete icon next to the key icon.
To define a key binding, drag a column from one input table and drop it onto the corresponding column in the other input table.
Set Lookup Element to the column under Lookup Input that will be used to replace the key columns in the output.
Selecting the Omit Non Matches checkbox will exclude rows where a match was not found in the lookup table.
Use the checkboxes to select or de-select columns for inclusion in the output.
3. Output
The figure below illustrates the output in our example.
