ACTIVE LookUp, To Unlock the Limitations of JOINER Transformation
Joiner Transformation can be used to achieve the functionality of SQL join Operation including full outer join. Additionally we can use Joiner to join data from heterogeneous data sources. But it is limited with the operators, which can be used in the join condition, it can use only 'equal to' operator in the join condition. In this article lets see how we can unlock this limitation using Informatica PowerCenter Active LookUp transformation.
To overcome this disability we will be using Active LookUp Transformation, which is available from Informatica PowerCenter Version 9x.
Choose the LookUp Table from the popup window and select 'Return All Values on Multiple Match'. This property will set the lookup as active lookup transformation.
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created.
To overcome this disability we will be using Active LookUp Transformation, which is available from Informatica PowerCenter Version 9x.
What is Active LookUp
From Informatica PowerCenter Version 9x onwards we can configure the lookup transformation to return all the rows from the lookup table matching the lookup condition. This Lookup transformation becomes an active transformation. For the Active LookUp 'Lookup Policy on Multiple Match' property will be 'Use All Values'. This property becomes read-only and cannot be changed after the transformation is created.
How to Configure Active LookUp
Just like any other transformation start creating the transformation.
Choose the LookUp Table from the popup window and select 'Return All Values on Multiple Match'. This property will set the lookup as active lookup transformation.
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created.
Unlock the JOINER Transformation Limitations
Lets consider a simple scenario where you are given with flat file with a list of customers and you need to pull all the orders from the relational table by a customer from a give date.
We can not use JOINER transformation to combine these two data sources and get all the orders form a customer just because of the fact that, we need to use greater than (>) operator to get all the records and only equal (=) operator is supported in JOINER.
So we can create the mapping with the Active LookUp Transformation to over come the limitation.
After the source definition is pulled into the designer, create the lookup transformation as shown below. Select 'Return All Values on Multiple Match'. to set the Active LookUp.
From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created.
Give the lookup condition to get all customer orders for the date DATE as in the below image.
Note : JOINER transformation does not allow operators other than equal (=).
After the active LookUp is configured, map all the columns to the target table. Below is the structure of the completed mapping.
Hope you enjoy this tutorial, Please let us know your comments and feedback.