When you configure a data transfer task, you can add up to five lookup sources. Configure lookup sources on the Lookup Sources page.
The task queries each lookup source based on the lookup condition that you specify and returns the result of the lookup to the target.
Add a lookup source when you want to augment the source data with a related value or values from the lookup source. For example, the source is an orders table that contains a customer ID field. You might retrieve the customer name and address from the lookup source so that you can include them in the target object.
To optimize performance, the task caches the lookup source. The cache remains static and does not change as the task runs. The task deletes the cache files after the task completes.
You can preview the data in the lookup source. The preview returns the first 10 rows. To preview rows in alphabetical order, select Display fields in alphabetical order. Data Integration does not change the order of rows in the actual source. You can also download the preview results to a CSV file.
You can also filter the combined source data before writing it to the target.
Lookup return fields
When you add a lookup source, you can select the fields to return from the source.
By default, the task returns all fields from the lookup source. If you don't want to use all fields, select Return selected and then choose the fields to return.
To avoid naming conflicts with other source fields, you can also rename the lookup return fields.
Multiplicity
By default, when a lookup condition returns multiple matches, the task returns an error. You can also configure the task to return a random row.
For each lookup source, configure how the task handles multiple return values. You can select the following multiplicity options:
•Error if more than 1 output value. The task displays an error. Data Integration rejects rows when multiple matches are found and writes them to the error rows file. This is the default.
•Randomly pick 1 output value. The task chooses the first returned value. Different systems might use different orders to return lookup values.
Lookup condition
When you configure a lookup source, you must define one or more lookup conditions.
A lookup condition defines when the lookup returns values from the lookup source. When you configure a lookup condition, you compare the value of one or more fields from the original source with values in the lookup source.
A lookup condition includes an incoming field from the original source, a field from the lookup source, and an operator. To avoid possible naming conflicts, you can rename fields in the source or in the lookup source.
For example, you might configure the following lookup condition when the original source contains the CustID field, the lookup source contains the CustomerID field, and you want to return values from the lookup source when the customer IDs match:
Lookup Field
Operator
Incoming Field
CustomerID
Equals
Src_CustID
You can use the following operators in a lookup condition:
•Equals
•Not Equals
•Less Than
•Less Than or Equals
•Greater Than
•Greater Than or Equals
When you enter multiple conditions, the task evaluates the lookup conditions using the AND logical operator to join the conditions. It returns rows that match all the lookup conditions.
When you include multiple conditions, to optimize performance, enter the conditions in the following order:
1Equals
2Less Than, Less Than or Equals, Greater Than, Greater Than or Equals
3Not Equals
The lookup condition matches null values. When an input field is NULL, the task evaluates the NULL equal to null values in the lookup.
Lookup source filters
You can apply filter conditions to filter the combined data.
You can configure the following types of filters:
Simple
To configure a filter condition, select a source field and configure the operator and value to use in the filter. You can select a field from either source.
When you define more than one filter condition, the task evaluates them in the order that you specify. The task evaluates the filter conditions using the AND logical operator to join the conditions. It returns rows that match all the filter conditions.
Advanced
Create a filter expression using the expression editor. You enter one expression that contains all filters. You can use source fields and built-in functions in the expression.