Getting Started

10.5
- 10.5.6
- 10.5.1
- 10.4.0

Back Next

Creating a Lookup Transformation

The source table in this mapping includes information about the manufacturer ID. However, you want the manufacturer name in the target table to make the summary data easier to read. In the following steps, you use a Lookup transformation to find each manufacturer name in the MANUFACTURERS table based on the manufacturer ID in the source table.

Create a Lookup transformation and name it LKP_Manufacturers.

The naming convention for Lookup transformations is LKP_
TransformationName
.

A dialog box prompts you to identify the source or target database to provide data for the lookup. When you run a session, the Integration Service must access the lookup table.

Click

Source

Select the MANUFACTURERS table from the list and click

Click

Done

to close the

Create Transformation

dialog box.

The Designer adds the transformation.

Use source and target definitions in the repository to identify a lookup source for the Lookup transformation. Alternatively, you can import a lookup source.

Open the Lookup transformation.

Add an input port, IN_MANUFACTURER_ID, with the same datatype as MANUFACTURER_ID.

In a later step, you connect the MANUFACTURER_ID port from the Aggregator transformation to this input port. IN_MANUFACTURER_ID receives MANUFACTURER_ID values from the Aggregator transformation. When the Lookup transformation receives a new value through this input port, it looks up the matching value from MANUFACTURERS.

By default, the Lookup transformation queries and stores the contents of the lookup table before the rest of the transformation runs, so it performs the join through a local copy of the table that it has cached.

Click the

Condition

tab, and click the

Add

button.

An entry for the first condition in the lookup appears. Each row represents one condition in the WHERE clause that the Integration Service generates when querying records.

Verify the settings for the condition.

The following table describes the settings for the condition:

Lookup Table Column	Operator	Transformation Port
MANUFACTURER_ID	=	IN_MANUFACTURER_ID

If the datatypes, including precision and scale, of these two columns do not match, the Designer displays a message and marks the mapping invalid.

View the

Properties

tab.

Do not change settings in this section of the dialog box.

Click

You now have a Lookup transformation that reads values from the MANUFACTURERS table and performs lookups using values passed through the IN_MANUFACTURER_ID input port. The final step is to connect this Lookup transformation to the rest of the mapping.

Click

Layout

Link Columns

Connect the MANUFACTURER_ID output port from the Aggregator transformation to the IN_MANUFACTURER_ID input port in the Lookup transformation.

Connect the MANUFACTURER_ID port in the LKP_MANUFACTURERS Lookup transformation object to the MANUFACTURER_ID port in the T_ITEM_SUMMARY target defintion object.

Click

Repository

Save

Creating a Mapping with Aggregate Values

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal