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.

Transform - Lookup
Transform - Lookup

1. Input

The Lookup transform requires two input transforms.

For example, the main input could be the following data, which includes a DepartmentID:

Input table example
Input table example

With the following department details used as a lookup input:

Lookup table example
Lookup table example

2. Configure

The Lookup configuration dialog lists the columns from the two input tables, and allows you to choose the replacement values.

Lookup configuration dialog
Lookup configuration dialog

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.

Lookup output example
Lookup output example

4. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri