Configuring Options

To specify the options for Table Lookup you create a rule. You can create multiple rules then specify the order in which you want to apply the rules. To create a rule, open the Table Lookup stage and click Add then complete the following fields.

Note: If you add multiple Table Lookup rules, you can use the Move Up and Move Down buttons to change the order in which the rules are applied.

Option

Description

Action

Specifies the type of action to take on the source field. One of the following:

Standardize
Changes the data in a field to match the standardized term found in the lookup table. If the field contains multiple terms, only the terms that are found in the lookup table are replaced with the standardized term. The other data in the field is not changed.
Identify
Flags the record as containing a term that can be standardized, but performs no action on the data in the field. The output field StandardizedTermIdentified is added to the record with a value of Yes if the field can be standardized and No if it cannot.
Categorize
Uses the Source value as a key and copies the corresponding value from the table into the field selected in the Destination list. This creates a new field in your data that can be used to categorize records.

On

Specifies whether to use the entire field as the lookup term or to search the lookup table for each term in the field. One of the following:

Complete field
Treats the entire field as one term, resulting in the following:
  • If you selected the action Standardize, Table Lookup treats the entire field as one string and attempts to standardize the field using the string as a whole. For example, "International Business Machines" would be changed to "IBM".
  • If you selected the action Identify, Table Lookup treats the entire field as one string and flags the record if the string as a whole can be standardized.
  • If you selected the action Categorize, Table Lookup treats the entire field as one string and flags the record if the string as a whole can be categorized.
Individual terms within field
Treats each word in the field as its own term, resulting in the following:
  • If you selected the action Standardize, Table Lookup parses the field and attempts to standardize the individual terms within the field. For example, "Bill Mike Smith" would be changed to "William Michael Smith."
  • If you selected the action Identify, Table Lookup parses the field and flags the record if any single term within the field can be standardized.
  • If you selected the action Categorize, Unlike Standardize, Categorize does not copy the source term if there isn't a table match. If none of the source terms match, Categorize uses the default value specified. Unlike Standardize, Categorize only returns that table value and nothing from Source. If none of the source terms match, Categorize uses the default value specified.

Source

Specifies the field you want to containing the term you want to look up.

Destination

Specifies the field to which the terms returned by the table lookup should be written.

If you want to replace the value, specify the same field in the Destination field as you did in the Source field. You can also create a new field by typing the name of the field you want to create.

The Destination field is not available if you select the action Identify.

Table

Specifies the table you want to use to find terms that match the data in your dataflow.

For a list of tables that you can edit, see Table Lookup Tables. For information about creating or modifying tables, see Introduction to Lookup Tables.

Lookup multiple word terms

Enables multiple word searches within a given string. For example:

Input String: "Major General John Smith"
Business Rule: Identify "Major General" in a string based on a table that contains the entry
Output: Replace "Major General" with "Maj. Gen."

For multiple word searches, the search stops at the first occurrence of a match.

This option is disabled when On is set to Complete field.

Note: Selecting this option may adversely affect performance.

When table entry not found, set Destination's value to

Specifies the value to put in the destination field if a matching term cannot be found in the lookup table. One of the following:

Source's value
Put the value from the source field into the destination field.
Other
Put a specific value into the destination field.