Lookup Activity

Use the Lookup Activity to retrieve rows of data from a selected database endpoint and use values from those rows to replace or add values to the current recurring element, based on an exact or approximate match of values from the current recurring element that is used as a key to perform the lookup.

To use the fuzzy lookup capability, you must use the Local Staging Database on the Integration Appliance. For non-fuzzy lookups, you can use any database endpoint that Studio supports.

Note: When the Lookup activity is run from Studio, lookup results are not cached.

Lookup activity requires a parameterized SQL query, an XPath query that indicates the recurring element, and an XPath query that indicates which value to use as the key for the lookup. A set of lookup values specifies how column values from the database result set should be mapped to elements or attributes of the current context node.

To complete a fuzzy lookup and approximately match the key, the Lookup activity uses the edit distance algorithm.

For example:

In the following table Names you want to perform a query on the last name Stark. The database contains the following records:
FirstName LastName
Tony Stark
Bruno Star
Georgia States
Howard Stark

Issue the following query: select * from Names where Col_LastName='Stark'

In this example, the key_length = length (“Stark”) = 5

For comparing against the threshold, use the value (1 – ( edit_distance / key_length) )

The edit distance is edit_distance (“Stark”, “Stark”) = 0 and edit_distance (“Stark", “Star”) = 1
  • To pick up only Stark use threshold (1 – (0/5)) = 1
  • To pick up both Stark and Star, but not States use threshold (1 - (1/5) ) = 0.8



Feedback | Notices


Timestamp icon Last updated: Tuesday, 27 September 2016


http://pic.dhe.ibm.com/infocenter/wci/v7r0m0/topic/com.ibm.wci.doc/lookup_activity.html