This Blog Explains the IICS Lookup Condition: Why Your Lookup returns NULL or Multiple Rows
What is IICS Lookup Condition in IICS?(Beginner Guide)
A Lookup Condition in IICS defines how a source row should match a row in the lookup table. It works exactly like a join condition in SQL. IICS compares the value from the source with the value in the lookup table and returns matching columns if a match is found.
For example, if your source contains customer orders, and your lookup table contains customer details, the matching rule would be:
Source.Customer_ID = Lookup.Customer_ID
When this condition is true, IICS returns all the selected ports from the lookup table, such as Customer Name, Status, Country, or Email.
Behind the scenes, Lookup transformation loads the lookup table into memory (if cached) and matches each incoming row against it. If the value is found, it returns the related information. If not found, the lookup returns NULL.
This is why clean data becomes extremely important. Even a small mismatch — extra spaces, datatype differences, lowercase vs uppercase, or duplicate keys — can confuse the lookup condition and produce incorrect results
IICS Lookup Condition Real Time Mistake #1 — Data Type Mismatch
❌ Mistake #1: Data Type Mismatch Between Source and Lookup
One of the most common issues beginners face is a datatype mismatch between the source column and the lookup column. Even though the values look the same to the human eye, IICS compares them based on their actual datatype in memory, not how they appear in the table.
For example, if the Source.customer_id is a string (VARCHAR) and Lookup.customer_id is stored as a number (INTEGER), the lookup will fail quietly and return NULL.
This happens because “C001” is not the same as C001, and “1001” is not equal to 1001 when datatypes differ.
Another common mistake is extra spaces or invisible characters in source files.
For example:
Source: “C001 ”
Lookup: “C001”
This will not match unless trimmed.
✅How to Fix It
1.Convert datatypes using Expression: TO_CHAR, TO_INTEGER
2. Always TRIM() values before lookup
3.Check metadata of both columns in mapping designer
4. Use Data Preview to confirm actual datatypes and values
Small datatype issues cause the biggest lookup failures — especially in projects where files come from external systems
Real-Time Mistake #2 — Duplicate Rows in Lookup Table
A very common reason why lookups fail in IICS is the presence of duplicate keys in the lookup table. Beginners assume lookup tables always contain unique values, but in real projects (especially customer, product, employee master data), duplicates are extremely common.
Example lookup table:
https://iicszone.com/connected-vs-unconnected-lookup-in-iics
| Customer_id | Customer_Name | Status |
| C0001 | Jennifer S | Active |
| C0001 | Jennifer S | Inactive |
When the source row contains C001, the lookup tries to match it. But now it finds two matching rows, causing the error:
“Lookup returned multiple rows”
This issue breaks the mapping and stops the entire pipeline.
✅ Why Do Duplicates Occur?
1. Multiple versions of the same record
2. Slowly changing dimensions stored incorrectly
3. No primary key defined on the table
4. Upstream systems sending multiple snapshots of a record
✅ How to Fix It
1. Use Sorter + Rank to pick the latest row
2. Or use Filter to remove old/inactive records
3. Or clean the table using SQL before loading into IICS
4. Always ensure lookup columns are close to unique
Conclusion 🙂
Lookup Conditions may look simple, but small issues like datatype mismatches and duplicate rows cause the majority of lookup failures in IICS. By understanding how matching works and cleaning your data before performing the lookup, you can avoid NULL returns, errors, and broken pipelines.
To fix this:
Make sure your data types match.
Clean your data and remove duplicates.
Understand how lookup matching works.
If you do these steps, your lookups will work better and your pipelines will run smoothly.
