Using the Lookup activity
About this task
Procedure
- In the Activities tab, open the Data Quality directory to find the Lookup activity.
- Add the activity to an orchestration. The Checklist and Summary for the activity are displayed beneath the orchestration.
- In the Checklist, select Pick
Endpoint and select a database endpoint. For instructions
on creating or editing a database endpoint, see Creating or
Editing a Database Activity Endpoint Note: To complete a fuzzy lookup you must select the Local Staging Database. To access the Local Staging Database:
- From the Database Type list, select Local. Staging opens in the Database Name field.
- In the Network Location Server field, specify the data IP address of the Integration Appliance, where the Local Staging Database is started. The Port is fixed at 1526.
- Enter a User Name and Password. Important: This login information must be for a user with Administrator or Publisher privileges on the Integration Appliance.
- Specify any additional parameters. For more details about the database parameters. see Creating or Editing a Database Activity Endpoint.
- Click OK. The Database endpoint is displayed in the Endpoints directory of the Studio toolbox.
- From the Checklist, select Configure and
specify the following information:
- The variable name of the variable that contains the recurring element, which the activity loops through.
- The element name of the recurring
element in the variable, which the activity loops through.
Click the browse button ( ... ) to open the Select Recurring Element window, where you can view all the elements in the variable you selected.
- In the Checklist, select Enter
Query
- Specify the query for the lookup table.
- For fuzzy lookups, you must specify exactly one parameter.
- For non-fuzzy lookups, you can specify one or more parameters.
- From the Encoding list, select the encoding type.
- Click Validate Query Note: If using a Local database endpoint, you must start the Local Staging Database on the Integration Appliance before you can view columns or validate queries. To start the Local Staging Database, in the Web Management Console (WMC), select Staging > Staging Database Start/Stop.
- Click View Columns and select a table to view detailed column information for the table you select. The Hide Buffer and Control Table option is the default selection.
- Specify the query for the lookup table.
- In the Checklist, select Setup
Input Parameters. The query you entered in the previous
step is displayed.
Specify the input XPath for each input parameter in the XPath column. The input XPath is an XPath expression that is relative to the recurring element which specifies the lookup key value that the query uses.
- In the Checklist, select Setup
Result Set.
- Specify the output XPath for each row in the result set. The output XPath specifies the element or attribute relative to the recurring element that will hold the output value.
- Use the at-sign ( @ ) at the beginning of the XPath
expression to specify an attribute.
- If the name of the element or attribute already exists, Studio replaces the existing value. Otherwise, Studio creates a new named element or attribute.
- If the lookup fails to find a match, the row is sent to the badXML element.
- In the Checklist, select Delivery
Rules and specify the following delivery rules:
Delivery Rule Description Activity Timeout Specifies in seconds the length of time that the Integration Appliance waits for the endpoint to respond. The default setting is 300 seconds. Fuzzy lookup Determines if the lookup is fuzzy. Select this option to perform a fuzzy lookup. Important: You can only perform a fuzzy lookup against a Local Database Endpoint.Note: Before you run the orchestration, use the Database Asset generation tool, in the WMC, to create an index table.Output at most __ match(es) per fuzzy lookup Specifies the maximum number of matches to output per fuzzy lookup. The similarity threshold __ for a fuzzy lookup Specifies the similarity threshold for determining a match during a fuzzy lookup (between 0 - 1). 1 = an exact match. Ignore case (for fuzzy lookup only) Select for your lookup to be case-insensitive. Ignore leading and trailing white spaces When selected, the Lookup activity ignores any white space that occurs before and after the data. For non-fuzzy lookups, you can only apply this option to the input value. For fuzzy lookups, you can apply this option to both the input value and the lookup table key. Preload cache Select this option to preload the lookup cache. Cache at most __ lookup results Specifies the maximum number of lookup results to cache.
What to do next
Parent topic: Lookup Activity