Table Lookups

Use Lookup Tables (LUTs) or set files to replace values

Overview
Aggregation
Cross-calculations
User-defined transformations
Formats/keys
Lookups
Match/Join
Pivot/Unpivot
Reassignment / reformatting
Scrubbing / cleaning
Select/Filter
Sort/Merge
Substrings
File type conversion
Challenges
Complex calculations – including multi-table joins, pseudonymization (substitution), and analytical output – can be expensive and slow. When a simple lookup can replace a runtime calculation, the performance gain can be significant, as retrieving a value from memory can be faster than calculating that value.

Lookups typically occur in ETL tools and databases. Lookup transformations require specific configuration, and additional steps may be needed to improve their performance with large volumes.

In the meantime, the analog functionality was not available in the file system, as more speed, resources, and simultaneous functionality can be available there.

Solutions

Multidimensional data tables can be looked up in both RDB and flat-file environments. The software products IRI CoSort (Data Transformation and Reporting) and IRI FieldShield (Data Masking) and the Data Management Platform IRI Voracity all lookup transformations in the pre-action and target layout definition phase of their jobs.

substitutions (replacement transformations) are from column values in a database or from values in an external tab-separated file (Set). The table search functions bypass the problems of database tuning and integrity and deliver:

  • Discrete Solutions (a Value Query Method)
  • A pseudonymization method for data security
  • An alternative to joins in many tables

By performing file system lookups through set files and an explicit job script, you can spend less time focusing on preparing and obtaining results from your data replacement sources.

In addition, you can:

  • Combining Lookups with Other Processes in the Same I/O Pass
  • Processing the resulting values in the same jobSortCL-Script)The
  • Encrypt or otherwise protect lookup results immediately
  • Simultaneously Formatting Lookup Results in Custom Reports
Related solutions
Product links