Preface
Introduction to Transformations
- Introduction to Transformations Overview
- Transformations in the Native and Non-native Environments
- Handling Transformation Data Types
- Developing a Transformation
- Multi-Group Transformations
  - Rules and Guidelines for Multi-Group Transformations
- Expressions in Transformations
- Local Variables
- Default Values for Ports
- Tracing Levels
- Reusable Transformations
- Non-Reusable Transformations
  - Editor Views for a Non-Reusable Transformation
- Creating a Transformation
Transformation Ports
- Transformation Ports Overview
- Create Ports
- Configure Ports
- Linking Ports
- Propagating Port Attributes
- Copying Ports from Excel
Transformation Caches
- Transformation Caches Overview
- Cache Types
- Cache Files
  - Cache File Directory
- Cache Size
  - Auto Cache Size
    - Maximum Memory for Auto Cache Size
  - Specific Cache Size
- Cache Size Increase by the Data Integration Service
- Cache Size for Partitioned Caches
- Cache Size Optimization
Address Validator Transformation
- Address Validator Transformation Overview
- Address Reference Data
  - Types of Address Reference Data
- Modes and Templates
- Port Groups and Port Selection
- Address Validator Transformation Input Port Groups
- Address Validator Transformation Output Port Groups
- Multiple-Instance Ports
- Address Validation Projects
- Formatted Addresses and Mail Carrier Standards
- Partial Address Completion
- Address Validator Status Ports
  - Element Status Code Definitions
  - Address Resolution Code Output Port Values
  - Element Input Status Output Port Values
  - Element Relevance Output Port Values
  - Element Result Status Output Port Values
  - Extended Element Result Status Output Port Values
  - Mailability Score Output Port Values
  - Match Code Output Port Values
  - Geocoding Status Output Port Values
- Address Validator Transformation General Settings
- Address Validation Properties in the Preferences Window
  - Address Validation Data Properties
  - Address Validation License Properties
  - Address Validation Engine Properties
- Address Validation Advanced Properties
  - Alias Locality
  - Alias Street
  - Casing Style
  - Country of Origin
  - Country Type
  - Default Country
  - Dual Address Priority
  - Element Abbreviation
  - Execution Instances
  - Flexible Range Expansion
  - Geocode Data Type
  - Global Max Field Length
  - Global Preferred Descriptor
  - Input Format Type
  - Input Format With Country
  - Line Separator
  - Matching Alternatives
  - Matching Extended Archive
  - Matching Scope
  - Max Result Count
  - Mode
  - Optimization Level
  - Output Format Type
  - Output Format With Country
  - Preferred Language
  - Preferred Script
  - Ranges To Expand
  - Standardize Invalid Addresses
  - Tracing Level
- Certification Reports
  - AMAS Report Fields
  - CASS Report Fields
  - SendRight Report
  - SERP Report Fields
- Configuring an Address Validator Transformation
- Adding Ports to the Address Validator Transformation
- Creating User-Defined Templates
- Defining Address Validator Models
- Defining a Certification Report
- Address Validator Transformation in a Non-native Environment
  - Address Validator Transformation on the Blaze Engine
  - Address Validator Transformation on the Spark Engine
    - Address Validator Transformation in a Streaming Mapping
  - Address Validator Transformation on the Databricks Spark Engine
Aggregator Transformation
- Aggregator Transformation Overview
- Aggregator Transformations in Dynamic Mappings
- Developing an Aggregator Transformation
- Aggregator Transformation Ports
- Aggregate Expressions
- Group By Ports
- Aggregator Caches
- Sorted Input for an Aggregator Transformation
  - Sorted Input Conditions
  - Sorting Data in an Aggregator Transformation
- Aggregator Transformation Advanced Properties
- Creating a Reusable Aggregator Transformation
- Creating a Non-Reusable Aggregator Transformation
- Tips for Aggregator Transformations
- Troubleshooting Aggregator Transformations
- Aggregator Transformation in a Non-native Environment
Association Transformation
- Association Transformation Overview
- Memory Allocation
- Association Transformation Advanced Properties
Bad Record Exception Transformation
- Bad Record Exception Transformation Overview
- Bad Record Exception Output Record Types
- Bad Record Exception Management Process Flow
- Bad Record Exception Mappings
  - Bad Record Exception Quality Issues
  - Human Tasks
- Bad Record Exception Ports
  - Bad Record Exception Transformation Input Ports
  - Bad Record Exception Transformation Output
- Bad Record Exception Configuration View
  - Generating the Bad Records Table and the Issues Table
- Bad Record Exception Issue Assignment
  - Assigning Ports to Quality Issues
- Exception Transformation Advanced Properties
- Configuring a Bad Record Exception Transformation
- Bad Record Exception Mapping Example
Case Converter Transformation
- Case Converter Transformation Overview
- Case Strategy Properties
- Configuring a Case Converter Strategy
- Case Converter Transformation Advanced Properties
- Case Converter Transformation in a Non-native Environment
Classifier Transformation
- Classifier Transformation Overview
- Classifier Models
- Classifier Algorithms
- Classifier Transformation Options
- Classifier Strategies
- Classifier Transformation Advanced Properties
- Configuring a Classifier Strategy
- Classifier Analysis Example
- Classifier Transformation in a Non-native Environment
Comparison Transformation
- Comparison Transformation Overview
- Field Matching Strategies
- Identity Matching Strategies
- Configuring a Comparison Strategy
- Comparison Transformation Advanced Properties
- Comparison Transformation in a Non-native Environment
Consolidation Transformation
- Consolidation Transformation Overview
- Consolidation Mappings
- Consolidation Transformation Ports
- Consolidation Transformation Views
- Simple Strategies
- Row-Based Strategies
- Advanced Strategies
- Simple Consolidation Functions
- Row-Based Consolidation Functions
- Consolidation Mapping Example
- Configuring a Consolidation Transformation
- Consolidation Transformation in a Non-native Environment
Data Masking Transformation
- Data Masking Transformation Overview
- Masking Techniques
- Masking Rules
- Special Mask Formats
- Default Value File
- Data Masking Transformation Configuration
- Data Masking Transformation Runtime Properties
- Data Masking Example
- Data Masking Transformation Advanced Properties
- Data Masking Transformation in a Non-native Environment
  - Data Masking Transformation on the Blaze Engine
  - Data Masking Transformation on the Spark Engine
    - Data Masking Transformation in a Streaming Mapping
Data Processor Transformation
- Data Processor Transformation Overview
- Data Processor Transformation Views
- Data Processor Transformation Ports
- Startup Component
- References
- Data Processor Transformation Settings
- Events
  - Event Types
  - Data Processor Events View
- Logs
- Data Processor Transformation Development
- Data Processor Transformation Export and Import
- Data Processor Transformation Validation
  - Using a Speed-enhanced Data Transformation Engine for VRL Validations
- Data Processor Transformation in a Non-native Environment
Decision Transformation
- Decision Transformation Overview
- Decision Transformation Functions
- Decision Transformation Conditional Statements
- Decision Transformation Operators
- Decision Transformation NULL Handling
- Configuring a Decision Strategy
- Decision Transformation Advanced Properties
- Decision Transformation in a Non-native Environment
  - Decision Transformation on the Spark Engine
  - Decision Transformation on the Databricks Spark Engine
Duplicate Record Exception Transformation
- Duplicate Record Exception Transformation Overview
- Duplicate Record Exception Process Flow
- Duplicate Record Exceptions
- Duplicate Record Exception Configuration View
  - Generating a Duplicate Records Table
- Ports
- Duplicate Record Exception Transformation Advanced Properties
- Duplicate Record Exception Mapping Example
- Creating a Duplicate Record Exception Transformation
Expression Transformation
- Expression Transformation Overview
- Expression Transformation Ports
- Test Expressions
  - Date Format Strings for Sample Data
  - Testing an Expression
- Port Selectors
- Windowing
  - Windowing Configuration
- Dynamic Expressions
  - Output Port Settings
  - Creating a Dynamic Expression
- Flatten a Dynamic Struct
- Expression Transformation Advanced Properties
- Expression Transformation in a Non-native Environment
Filter Transformation
- Filter Transformation Overview
- Filter Transformations in Dynamic Mappings
- Filter Condition
  - Parameterize the Filter Condition
  - Filtering Rows with Null Values
- Filter Transformation Advanced Properties
- Filter Transformation Performance Tips
- Filter Transformation in a Non-native Environment
  - Filter Transformation on the Blaze Engine
Hierarchical to Relational Transformation
- Hierarchical to Relational Transformation Overview
- Example - Hierarchical to Relational Transformation
- Output Relational Ports and the Overview View
- Hierarchical to Relational Transformation Ports
- Schema References
- Port Configuration
- Hierarchical to Relational Transformation Development
Java Transformation
- Java Transformation Overview
- Designing a Java Transformation
- Java Transformation Ports
  - Creating Ports
  - Setting Default Port Values
    - Input and Output Ports
- Java Transformation Advanced Properties
  - Configuring the Classpath for the Developer Tool Client
  - Configuring the Classpath for the Data Integration Service
- Developing Java Code
- Java Transformation Java Properties
- Filter Optimization with the Java Transformation
  - Early Selection Optimization with the Java Transformation
  - Push-Into Optimization with the Java Transformation
- Creating a Java Transformation
  - Creating a Reusable Java Transformation
  - Creating a Non-Reusable Java Transformation
- Compiling a Java Transformation
- Troubleshooting a Java Transformation
  - Finding the Source of Compilation Errors
    - Finding an Error on a Code Entry Tab or the Full Code Tab
  - Identifying the Source of Compilation Errors
    - User Code Errors
    - Non-User Code Errors
- Converting to Struct Data Example
- Java Transformation in a Non-native Environment
  - Java Transformation on the Blaze Engine
  - Java Transformation on the Spark Engine
    - Java Transformation in a Streaming Mapping
Java Transformation API Reference
- Java Transformation API Methods Overview
- defineJExpression
- failSession
- generateRow
- getInRowType
- getMetadata
- incrementErrorCount
- invokeJExpression
- isNull
- logError
- logInfo
- resetNotification
- setNull
- storeMetadata
Java Expressions
- Java Expressions Overview
  - Expression Function Types
- Using the Define Function Dialog Box to Define an Expression
- Working with the Simple Interface
  - invokeJExpression
  - Simple Interface Example
- Working with the Advanced Interface
- JExpression Class API Reference
Joiner Transformation
- Joiner Transformation Overview
- Joiner Transformation Advanced Properties
- Joiner Caches
- Joiner Transformation Ports
- Joiner Transformations in Dynamic Mappings
- Port Selectors in a Joiner Transformation
  - Selection Rules
  - Creating a Port Selector
- Defining a Join Condition
- Join Types
- Sorted Input for a Joiner Transformation
- Joining Data from the Same Source
- Blocking the Source Pipelines
  - Unsorted Joiner Transformation
  - Sorted Joiner Transformation
    - Caching Master Rows
- Joiner Transformation Performance Tips
- Rules and Guidelines for a Joiner Transformation
- Joiner Transformation in a Non-native Environment
Key Generator Transformation
- Key Generator Transformation Overview
- Soundex Strategy
  - Soundex Strategy Properties
- String Strategy
  - String Strategy Properties
- NYSIIS Strategy
- Key Generator Output Ports
- Configuring a Grouping Strategy
- Key Creation Properties
- Key Generator Transformation Advanced Properties
- Key Generator Transformation in a Non-native Environment
Labeler Transformation
- Labeler Transformation Overview
- When to Use a Labeler Transformation
- Reference Data Use in the Labeler Transformation
- Labeler Transformation Strategies
  - Character Labeling Operations
  - Token Labeling Operations
- Labeler Transformation Ports
- Character Labeling Properties
- Token Labeling Properties
- Configuring a Character Labeling Strategy
- Configuring a Token Labeling Strategy
- Labeler Transformation Advanced Properties
- Labeler Transformation in a Non-native Environment
Lookup Transformation
- Lookup Transformation Overview
- Connected and Unconnected Lookups
  - Connected Lookups
  - Unconnected Lookups
- Developing a Lookup Transformation
- Lookup Query
- Lookup Source Filter
  - Filtering Source Rows in a Lookup
- Lookup Condition
  - Configure the Lookup Condition
  - Rules and Guidelines for Lookup Transformation Conditions
- Lookup Cache
- Query Properties
- Lookup Transformations in Dynamic Mappings
- Define Dynamic Ports
- Change the Lookup Source
- Port Selectors
- Run-time Properties
- Advanced Properties
- Creating a Reusable Lookup Transformation
- Creating a Non-Reusable Lookup Transformation
- Creating an Unconnected Lookup Transformation
- Unconnected Lookup Example
- Lookup Transformation in a Non-native Environment
Lookup Caches
- Lookup Caches Overview
- Lookup Cache Types
- Uncached Lookup
- Static Lookup Cache
- Persistent Lookup Cache
  - Rebuilding a Persistent Lookup Cache
- Dynamic Lookup Cache
- Shared Lookup Cache
  - Rules and Guidelines for Sharing a Lookup Cache
- Cache Comparison
- Cache Partitioning for Lookups
Dynamic Lookup Cache
- Dynamic Lookup Cache Overview
- Uses for a Dynamic Lookup Cache
- Dynamic Lookup Cache Properties
- Dynamic Lookup Cache and Output Values
- Lookup Transformation Values
  - Lookup Transformation Values Example
- SQL Override and Dynamic Lookup Cache
- Mapping Configuration for a Dynamic Lookup Cache
- Conditional Dynamic Lookup Cache Updates
  - Conditional Dynamic Lookup Cache Processing
  - Configuring a Conditional Dynamic Lookup Cache
- Dynamic Cache Update with Expression Results
- Dynamic Lookup Cache Example
- Rules and Guidelines for Dynamic Lookup Caches
Macro Transformation
- Macro Transformation Overview
- Macro Instruction
- Macro Transformation Parameters
- Macro Transformations in Dynamic Mappings
- Macro Transformation Example
- Macro Transformation in a Non-native Environment
Match Transformation
- Match Transformation Overview
- Match Analysis
- Match Score Calculations
- Master Data Analysis
  - Mapping Reuse
- Identity Match Analysis and Persistent Index Data
  - Rules and Guidelines for Persistent Index Data
- Match Mapping Performance
  - Viewing Match Cluster Analysis Data
  - Viewing Match Performance Analysis Data
- Match Performance in Identity Analysis
  - Creating a Data Store for Identity Index Data
  - Using the Index Data Store in Single-Source Analysis
- Match Transformation Views
- Match Transformation Ports
- Match Mapplets
  - Creating a Match Mapplet
  - Using a Match Mapplet
- Configuring a Match Analysis Operation
- Match Transformation in a Non-native Environment
Match Transformations in Field Analysis
- Field Match Analysis
- Process Flow for Field Match Analysis
- Field Match Type Options
- Field Match Strategies
  - Field Match Algorithms
  - Field Match Strategy Properties
- Field Match Output Options
  - Match Output Types
  - Match Output Properties
- Field Match Advanced Properties
- Field Match Analysis Example
Match Transformations in Identity Analysis
- Identity Match Analysis
- Process Flow for Identity Match Analysis
- Identity Match Type Properties
  - Index Directory and Cache Directory Properties
  - Persistence Method Parameters
- Identity Match Strategies
  - Identity Match Algorithms
  - Identity Match Strategy Properties
- Identity Match Output Options
  - Match Output Types
  - Match Output Properties
    - Match Property Configuration
    - Output Property Configuration
- Identity Match Advanced Properties
- Persistent Index Case Study
- Identity Match Analysis Example
Normalizer Transformation
- Normalizer Transformation Overview
- Multiple-Occurring Fields
  - Generated Column ID
- Multiple-Occurring Records
- Input Hierarchy Definition
- Normalizer Transformation Output Groups and Ports
  - Create an Output Group
  - Update an Output Group
- Key Generation for Output Groups
- Normalizer Transformation Advanced Properties
  - Generate First Level Output Groups
- Creating a Normalizer Transformation
- Creating a Normalizer Transformation from an Upstream Source
- Normalizer Mapping Example
- Normalizer Transformation in a Non-native Environment
Merge Transformation
- Merge Transformation Overview
- Configuring a Merge Strategy
- Merge Transformation Advanced Properties
- Merge Transformation in a Non-native Environment
Parser Transformation
- Parser Transformation Overview
- Parser Transformation Modes
- When to Use a Parser Transformation
- Reference Data Use in the Parser Transformation
- Token Parsing Operations
- Token Parsing Ports
- Token Parsing Properties
- Pattern-Based Parsing Mode
  - Pattern-Based Parsing Ports
- Configuring a Token Parsing Strategy
- Configuring a Pattern Parsing Strategy
- Parser Transformation Advanced Properties
- Parser Transformation in a Non-native Environment
Python Transformation
Rank Transformation
- Rank Transformation Overview
  - Ranking String Values
  - Rank Transformation Properties
- Rank Transformations in Dynamic Mappings
- Rank Transformation Ports
  - Rank Index
- Rank Port
- Define Group By Ports
  - Group By Parameters
- Rank Caches
- Rank Transformation Advanced Properties
- Rank Transformation in a Non-native Environment
Read Transformation
- Read Transformation Overview
- Read Transformation Properties
- Synchronize Relational Data Objects
- Change the Source Data Object
  - Parameterize the Read Transformation
- Read Transformation Parameters
- Constraints
- Create a Read Transformation
  - Creating a Read Transformation in the Mapping Editor
Relational to Hierarchical Transformation
- Relational to Hierarchical Transformation Overview
- Example - Relational to Hierarchical Transformation
- Input Relational Ports and the Overview View
- Relational to Hierarchical Transformation Ports
- Schema References
- Relational to Hierarchical Transformation Development
  - Creating the Relational to Hierarchical Transformation
  - Creating the Ports
REST Web Service Consumer Transformation
- REST Web Service Consumer Transformation Overview
  - REST Web Service Consumer Transformation Process
- REST Web Service Consumer Transformation Configuration
  - Message Configuration
  - Resource Identification
- HTTP Methods
- REST Web Service Consumer Transformation Ports
- REST Web Service Consumer Transformation Input Mapping
  - Rules and Guidelines to Map Input Ports to Elements
  - Mapping Input Ports to the Method Input
- REST Web Service Consumer Transformation Output Mapping
- REST Web Service Consumer Transformation Advanced Properties
- REST Web Service Consumer Transformation Creation
  - Creating a REST Web Service Consumer Transformation
- Parsing a JSON Response Message that Contains Arrays
  - Example JSON Response Message
  - Unnamed Arrays in a Response Message
Router Transformation
- Router Transformation Overview
- Router Transformations in Dynamic Mappings
- Working with Groups
- Working with Ports
- Connecting Router Transformations in a Mapping
- Router Transformation Advanced Properties
- Router Transformation in a Non-native Environment
Sequence Generator Transformation
- Sequence Generator Transformation Overview
- Sequence Generator Ports
  - Pass-Through Ports
  - NEXTVAL Port
    - Create Keys
    - Replace Missing Values
- Sequence Generator Transformation Properties
- Sequence Generator Advanced Properties
  - Reset
  - Maintain Row Order
- Sequence Data Object
  - Creating a Sequence Data Object
- Creating a Sequence Generator Transformation
- Frequently Asked Questions
- Sequence Generator Transformation in a Non-native Environment
  - Sequence Generator Transformation on the Blaze Engine
  - Sequence Generator Transformation on the Spark Engine
Sorter Transformation
- Sorter Transformation Overview
- Sorter Transformations in Dynamic Mappings
- Developing a Sorter Transformation
- Sorter Transformation Ports
- Sort Tab
- Configure Sort Keys
  - Parameterize the Sort Keys
- Sorter Transformation Advanced Properties
- Sorter Cache
  - Optimizing the Sorter Cache
- Creating a Sorter Transformation
  - Creating a Reusable Sorter Transformation
  - Creating a Non-Reusable Sorter Transformation
- Sorter Transformation Example
- Sorter Transformation in a Non-native Environment
SQL Transformation
- SQL Transformation Overview
- SQL Transformation Ports
- SQL Transformation Advanced Properties
- SQL Transformation Query
  - Define the SQL Query
    - Parameter Binding
    - String Substitution
- Input Row to Output Row Cardinality
- Filter Optimization with the SQL Transformation
  - Early Selection Optimization with the SQL Transformation
    - Enabling Early Selection Optimization with the SQL Transformation
  - Push-Into Optimization with the SQL Transformation
    - Push-Into Optimization with the SQL Transformation Example
    - Enabling Push-Into Optimization with the SQL Transformation
- SQL Transformation Example with an SQL Query
- Stored Procedures
- SQL Transformation Connection
  - Creating a Connection Name Parameter
- Manually Creating an SQL Transformation
- Creating an SQL Transformation from a Stored Procedure
Standardizer Transformation
- Standardizer Transformation Overview
- Standardization Strategies
- Standardization Properties
- Configuring a Standardization Strategy
- Standardizer Transformation Advanced Properties
- Standardizer Transformation in a Non-native Environment
Union Transformation
- Union Transformation Overview
- Groups and Ports
- Union Transformation Advanced Properties
- Union Transformation Processing
- Creating a Union Transformation
  - Creating a Reusable Union Transformation
  - Creating a Non-Reusable Union Transformation
- Union Transformation in a Non-native Environment
  - Union Transformation in a Streaming Mapping
  - Union Transformation on the Databricks Spark Engine
Update Strategy Transformation
- Update Strategy Transformation Overview
  - Setting the Update Strategy
- Update Strategy Transformations in Dynamic Mappings
- Flagging Rows Within a Mapping
- Specifying Update Options for Individual Targets
- Update Strategy Transformation in a Non-native Environment
Web Service Consumer Transformation
- Web Service Consumer Transformation Overview
- WSDL Selection
- Web Service Consumer Transformation Ports
  - HTTP Header Input Ports
  - Other Input Ports
- Web Service Consumer Transformation Input Mapping
- Web Service Consumer Transformation Output Mapping
- Web Service Consumer Transformation Advanced Properties
- Filter Optimizations
  - Enabling Early Selection Optimization with the Web Service Consumer Transformation
  - Push-Into Optimization with the Web Service Consumer Transformation
    - Push-Into Optimization with Web Service Consumer Transformation Example
    - Enabling Push-Into Optimization with the Web Service Consumer Transformation
- Creating a Web Service Consumer Transformation
- Web Service Consumer Transformation Example
Parsing Web Service SOAP Messages
- Parsing Web Service SOAP Message Overview
- Transformation User Interface
- Multiple-Occurring Output Configuration
- Parsing anyType Elements
- Parsing Derived Types
- Parsing QName Elements
- Parsing Substitution Groups
- Parsing XML Constructs in SOAP Messages
Generating Web Service SOAP Messages
- Generating Web Service SOAP Messages Overview
- Transformation User Interface
  - Input Ports Area
  - Operation Area
- Port and Hierarchy Level Relationships
- Keys
- Map Ports
- Pivoting Multiple-Occurring Ports
- Map Denormalized Data
- Derived Types and Element Substitution
- Generating XML Constructs in SOAP Messages
Weighted Average Transformation
- Weighted Average Transformation Overview
- Configuring a Weighted Average Transformation
- Weighted Match Scores Example
- Weighted Average Transformation Advanced Properties
- Weighted Average Transformation in a Non-native Environment
Window Transformation
Write Transformation
- Write Transformation Overview
- Write Transformation Properties
- Create a Write Transformation
Appendix A: Transformation Delimiters
- Transformation Delimiters Overview

Developer Transformation Guide

10.5
- 10.5.2
- 10.4.1
- 10.4.0

Back Next

Lookup Query

The Integration Service queries the lookup based on the ports and properties you configure in the Lookup transformation. The Integration Service runs a default lookup query when the first row enters the Lookup transformation.

If you use a lookup against a relational table, you can override the lookup query.

You can use the override to add a WHERE clause or transform the lookup data before it is cached.

If you configure a SQL override and a filter on the lookup query, the Integration Service ignores the filter.

Lookup Transformation

Default Lookup Query

SQL Override for a Lookup Query

Parameters in an SQL Override Query

Reserved Words

Guidelines for Overriding the Lookup Query

Overriding the Lookup Query

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Developer Transformation Guide

Developer Transformation Guide

Lookup Query

Lookup Query