Configuring Options

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.

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.