Informatica Common Components Lookup nine configure a Lookup transformation that is not connected

In the map, the non-connected lookup transformation is separate from the pipeline. You can use the: LKP reference qualifier to write an expression to invoke lookups in other transformations. Common uses for non-connected lookups include:

• Test the result of a lookup in an expression

• Filter rows based on search results

• Mark rows as updates based on found results, such as a dimension table with updated slow changes

• Calls the same lookup multiple times in a map

Complete the following steps when you configure a Lookup transformation that is not connected:

• Add an input port.

• Add a Find condition.

• Specifies a return value.

• Invokes a lookup in another transformation.

Step 1. Add Input port

To: Create an input port for each parameter in the LKP expression. You need to add an input port in the Lookup transformation for each lookup condition that you want to create. You can create different ports for each condition, or use the same input port in multiple conditions.

For example, a retail store raised prices between all departments last month. The accounting department only wants to load the line of the item that raised the price into the target. To complete this operation, perform the following tasks:

• Creates a lookup condition that compares the item_id in the source with the item_id in the target.

• Compare prices in the price and target tables for each item in the source.

• If the item already exists in the target table, and the item's price in the source is less than or equal to the price in the target table, you need to delete the row.

• If the price in the source is higher than the item in the target table, you need to update the row.

• Create an input port (in_item_id) with a data type of decimal (37,0) to match item_id, and create an input port In_price with a data type of decimal (10,2) to match the price lookup port.

Step 2. Add a Find condition

After the port is configured correctly, define a lookup condition that is used to compare the values in the transformation input value and the lookup source or cache. To improve performance, first add a condition with an equals sign.

In this example, add the following lookup criteria:

item_id = in_item_id Price <= in_price

If the item exists in the map source and the lookup source, and the map source price is less than or equal to the lookup price, the condition is true, and the value returned by the return port is checked.　　If the lookup condition is not true, the lookup returns NULL. Therefore, when you write an update policy expression, you use ISNULL in IIF to test for null values.

Step 3. Specify a return value

For non-connected lookups, you can pass multiple input values into the transformation, but only one column of data is output from the transformation. Specifies a lookup/output port as the return port. PowerCenter can return a value from a lookup query. Use the return port to specify the return value. If you call a non-connected lookup from an update policy or filter expression, you can fully check for the existence of a null value. In this case, the return port can be any port. However, if you call find from an expression that performs a calculation, the return value must be the value that you want the calculation to contain.

To continue updating the policy example, you can define the ITEM_ID port as a return port. The update policy expression checks for null values that are returned. If the lookup condition is true, PowerCenter will return item_id. If the condition is not true, PowerCenter Server will return NULL.

Step 4. Call lookup through an expression

from other converted: LKP expressions, provide input values for the non-connected lookup transformation. The parameter is a local input port that matches the Lookup transformation input port used in the lookup condition. Please use the following: LKP expression syntax:

: Lkp.lookup_transformation_name ( argument, argument, ... )

Continue to use the previous retail store as an example, when you write an update policy expression, the ports in the expression must be in the same order as they were in the lookup condition. In this case, the item_id condition is the first lookup condition, so it is the first parameter in the update policy expression.

IIF (ISNULL (: Lkp.lkpitems_dim (item_id, Price)), Dd_update, Dd_reject)

When you write an expression that calls a non-connected lookup transformation, follow these guidelines:

• The order of the arrangement parameters must match the order in which the lookup criteria are found in the lookup transformation.

• The data type of the port in the expression must match the data type of the input port in the Lookup transformation. The Designer does not verify that the data type of the expression matches.

• If a port in the lookup condition is not a Find/output port, the Designer does not validate the expression.

• The order of the parameters (ports) in an expression must be the same as the order in which the ports are entered in the lookup condition.

• If you use the incorrect: LKP syntax, Designer will mark the mapping as invalid.

• If the connected Lookup transformation is called in the: LKP expression, the Designer marks the mapping as invalid.

Tip: Select functions and Ports by using Point-and-click mode to avoid syntax errors when entering expressions.

 

Informatica Common Components lookup nine configure a Lookup transformation that is not connected