Multiple Matches

Return First Row; Return Last Row

The transformation returns the first matching result or the last matching result.

The Data Integration Service orders results to identify the first and last row. The following rules determine result order:

Ordering depends on the lookup ports present in the lookup condition and lookup output ports.
Numerical value sorting is by ascending order.
String value sorting is lexographical.
Date value sorting is by earliest date first.
When mappings contain a non-equijoin(<=,>=,<,>,!=) comparison and some rows contain NULL values, results might be different depending on the run-time engine:
On the Spark engine, NULL results are treated as TRUE. For more information about how Spark processes null values, see the Apache documentation: https://spark.apache.org/docs/3.0.0-preview/sql-ref-null-semantics.html#comp-operators
On the Blaze or native engines, NULL results are treated as FALSE.

Return Any Row

The transformation returns any of the rows that match the lookup condition. The transformation creates an index based on the key ports instead of all Lookup transformation ports. When you choose this option, performance can improve because the process of indexing rows is simpler.

Return All Rows

The Lookup transformation returns all rows that match the lookup condition.

Report Error

When the Lookup transformation uses a static cache or no cache, the Data Integration Service marks the row as an error. The Lookup transformation writes the row to the session log by default, and increases the error count by one.

When the Lookup transformation has a dynamic cache, the Data Integration Service fails the session when it encounters multiple matches. The session fails while the Data Integration Service is caching the lookup table or looking up the duplicate key values.

Also, if you configure the Lookup transformation to output old values on updates, the Lookup transformation returns an error when it encounters multiple matches. The transformation creates an index based on the key ports instead of all Lookup transformation ports.

Rules and Guidelines