Managing Lookups
You can add a lookup intermediate target when you want to use a field (or set of fields) from the data source as a "key" into some other data source (the lookup) to retrieve a different lookup field value (or set of field values) that you want to map into a final target. A classic example is the following:
• Source data contains a field with a state code. For example, "TX"
• Final target must have the full state name (Texas)
• A file or database table has columns that contains the state code and the full name
What to do:
• Add a lookup and configure it to point to the file or database that has both the state code and the full name
• Map the source state code into the lookup state code column of the lookup target (this sets the key for the lookup)
• Map the lookup state name column into the final target state name field and the final target will have the full state name.
To write the results of a lookup to a target field, you must add a lookup to a map. To write the results of an incore lookup that includes default values, you must update the schema to include the default values and use the updated schema in the map.
The Schema Mismatch property tells the transformation engine what to do when the lookup map schema does not match the lookup schema that it is connected to at run time.
Lookup Types
The supported lookup types are:
• Flatfile Lookup: This is the default lookup. Considerations when using a flat file lookup:
– Only connectors that supply a file can be used.
– The field separator and encoding connector properties are used, but all other properties (such as headers and field delimiters) are ignored.
– The record separator must be CR, LF, or CR-LF.
– The default field separator is Tab if none is provided in the connector properties.
– The default encoding is OEM if none is supplied in the connector properties.
– The last line must have a record separator.
– The maximum key length is 31 characters.
• Incore Lookup: Incore lookups are stored in memory (RAM). If there are errors associated with the building of, or reference to, an Incore lookup, check the amount of available RAM. Depending on the amount of data being stored, up to 2 GB of RAM can be used for a single lookup. Configure the incore lookup source to point to the lookup data. During runtime, the data will be retrieved and stored in memory. This improves the speed of the lookups if performance is an issue. If the source uses an SQL connector, a query can be used for the lookup. Incore is the default lookup type.
• DynamicSQL Lookup: These lookups are used when the lookup data is defined in a database that supports SQL but the data is too large to be placed in memory. As a result, these lookups are slower than incore lookups.
• OneToMany Lookup: The one-to-many flat file lookup builds an in-memory index to the appropriate records. Once the index is built, as it needs to read values they are retrieved from the file using the connector.
Adding Lookups
To add a lookup in a map:
1. On the Source tab, click 
> Lookup, select the type of lookup you want to configure.
> Lookup, select the type of lookup you want to configure.2. Change the name of the Lookup and add a description (if required).
3. Configure all the connector parts and properties related to the lookup data.
4. When defining the lookup keys, you will be "Mapping Into" your lookup. Make sure to choose the lookup name in the Mapping filter or select it in the Map Navigator.
5. When mapping lookup results into a main target, choose the main target name in the Map Into filter or select it in the Map Navigator.
6. In the Source section of the map, the lookup fields must be displayed, where you can map the result field you want into any target field.
Deleting Lookups
To delete a lookup, from the Source drop-down list, select the lookup and click 
. The lookup is deleted and does not appear in Map Navigator.
. The lookup is deleted and does not appear in Map Navigator. Last modified date: 02/09/2024