You can use an unconnected Lookup transformation to replace cryptic or numeric ID values in a table with meaningful names from a lookup table.
For example, you need to load some sales order data from SAP transactional tables to a relational table in your data warehouse. The SAP tables contain numeric IDs for values such as the sales group and sales office. In the data warehouse table, you want to replace the numeric IDs with the corresponding names in your local language. The name that is associated with each ID is stored in a reference table. Use an unconnected Lookup transformation to retrieve the names from the reference table.
The following image shows the mapping:
Configure the transformations in the following ways:
Source transformation
Use a Source transformation to specify the tables from which to extract data.
On the Source tab, configure the source connection and select the tables from which you want to extract data.
First Expression transformation (optional)
Optionally, use an Expression transformation to rename fields and replace null values.
On the Incoming Fields tab, use the Named Fields field selection criteria to select the fields that you want to load to the target table. If required, rename the selected fields to give them more meaningful names.
On the Expression tab, create output fields to replace the null values. For example, to replace null values for the sales group code and sales office code with spaces, you might create the following output fields:
Use an unconnected Lookup transformation to retrieve the descriptions from the reference table.
On the General tab, enable the Unconnected Lookup option.
On the Incoming Fields tab, create an incoming field for each value that you need to pass to the Lookup transformation to retrieve the data that you want. For example, to pass the domain name, language, and code value to the Lookup transformation, create the in_domain_name, in_language, and in_lookup_code fields.
On the Lookup Object tab, configure the lookup connection and select the reference table that you want to use as the lookup table.
On the Lookup Condition tab, specify the lookup condition for each incoming field. For example:
Lookup Field
Operator
Incoming Field
domain_name
=
in_domain_name
language_code
=
in_language
lookup_code
=
in_lookup_code
On the Return Fields tab, select the field in the reference table that you want to return. For example, to return a description, you might select lookup_description as the return field.
Second Expression transformation
Use an Expression transformation to call the unconnected Lookup transformation and retrieve the name that is associated with each ID value.
On the Incoming Fields tab, include all fields from the upstream transformation.
On the Expression tab, create an output field to retrieve each description from the Lookup transformation. Call the Lookup transformation with a :LKP expression. For example, to retrieve the sales group and sales office names from the appropriate domain in English, you might create the following output fields:
On the Target tab, configure the target connection and select the relational table to which you want to load data.
On the Field Mapping tab, map the output fields from the upstream transformation to the appropriate target fields. For example, to map the sales_group and sales_office output fields from the second Expression transformation to the SALES_GROUP and SALES_OFFICE target fields, configure the following field mapping:
