Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

Unconnected Lookup example

Unconnected Lookup example

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:
The mapping contains an unconnected Lookup transformation and the following connected transformations: a Source transformation, an Expression transformation that selects fields and replaces null values, an Expression transformation that call the unconnected Lookup transformation, and a Target transformation.
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:
Output Field
Expression
in_sales_group
IIF(ISNULL(sales_group_code),' ',sales_group_code)
in_sales_office
IIF(ISNULL(sales_office_code),' ',sales_office_code)
Unconnected Lookup transformation
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:
Output Field
Expression
sales_group
:LKP.lkp_Descriptions('sales_group','en',in_sales_group)
sales_office
:LKP.lkp_Descriptions('sales_office','en',in_sales_office)
Target transformation
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:
Target Field
Mapped Field
SALES_GROUP
sales_group
SALES_OFFICE
sales_office