Table of Contents

Search

  1. Preface
  2. Introduction to Transformations
  3. Transformation Ports
  4. Transformation Caches
  5. Address Validator Transformation
  6. Aggregator Transformation
  7. Association Transformation
  8. Bad Record Exception Transformation
  9. Case Converter Transformation
  10. Classifier Transformation
  11. Comparison Transformation
  12. Consolidation Transformation
  13. Data Masking Transformation
  14. Data Processor Transformation
  15. Decision Transformation
  16. Duplicate Record Exception Transformation
  17. Expression Transformation
  18. Filter Transformation
  19. Hierarchical to Relational Transformation
  20. Java Transformation
  21. Java Transformation API Reference
  22. Java Expressions
  23. Joiner Transformation
  24. Key Generator Transformation
  25. Labeler Transformation
  26. Lookup Transformation
  27. Lookup Caches
  28. Dynamic Lookup Cache
  29. Match Transformation
  30. Match Transformations in Field Analysis
  31. Match Transformations in Identity Analysis
  32. Normalizer Transformation
  33. Merge Transformation
  34. Parser Transformation
  35. Python Transformation
  36. Rank Transformation
  37. Read Transformation
  38. Relational to Hierarchical Transformation
  39. REST Web Service Consumer Transformation
  40. Router Transformation
  41. Sequence Generator Transformation
  42. Sorter Transformation
  43. SQL Transformation
  44. Standardizer Transformation
  45. Union Transformation
  46. Update Strategy Transformation
  47. Web Service Consumer Transformation
  48. Parsing Web Service SOAP Messages
  49. Generating Web Service SOAP Messages
  50. Weighted Average Transformation
  51. Window Transformation
  52. Write Transformation
  53. Appendix A: Transformation Delimiters

Developer Transformation Guide

Developer Transformation Guide

Lookup Condition

Lookup Condition

The Data Integration Service looks up data in the lookup source based on a lookup condition. When you configure a lookup condition in a Lookup transformation, you compare the value of one or more columns in the source data with values in the lookup source or cache.
For example, the source data contains an employee_number. The lookup source table contains employee_ID, first_name, and last_name. You configure the following lookup condition:
employee_ID = employee_number
For each employee_number, the Data Integration Service returns the employee_ID, last_name, and first_name column from the lookup source.
The Data Integration Service can return more than one row from the lookup source. You configure the following lookup condition:
employee_ID > employee_number
The Data Integration Service returns rows for all employee_ID numbers greater than the source employee number.

Null Values in a Data Object Lookup

When an input to the Lookup condition is NULL, a data object Lookup transformation returns a single row with null values for output-only ports, and values from input rows for pass-through ports.
For example, the following lookup condition performs a lookup on a data source that contains one or more rows where the value for employee_ID is NULL:
employee_ID = employee_number
In this example, you use a lookup table with the following data:
EMPLOYEE_ID
LAST_NAME
1294765
Hara
1356356
Carver
1407207
NULL
1570348
Draper
NULL
Limonov
You compare the following input values from your data source with the lookup table:
EMPLOYEE_NUMBER --------------- 1294765 1356356 1407207 1648246 NULL
In this example, the Lookup condition produces the following results:
1294765,Hara 1356356,Carver 1407207,NULL NULL,NULL NULL,NULL
The Lookup condition finds matches between EMPLOYEE_ID and EMPLOYEE_NUMBER for the first two rows. For the third row, the lookup source contains a row with a NULL value that does not participate in the lookup condition. It matches the lookup condition, and returns a result with the NULL value for the non-lookup column.
For the fourth and fifth rows, the Lookup condition does not find a match, and returns NULL for both values. For the fifth row, note that the Lookup condition does not find a match again, because NULL does not match anything, including NULL.

0 COMMENTS

We’d like to hear from you!