Scenario 26: How to create and use Active Lookup Transformation?

Scenario 26: How to create and use Active Lookup Transformation?

What is Active Lookup Transformation?
When lookup transformation return all the matching records from the Lookup table then it's called as an Active transformation.
This feature of Lookup has been introduced from 9.X version of Informatica Power Center tool.

Business Requirement: We have two table Product and currency now in the target table we have to populate all products with price after taking the all currency rate from currency table.

Note: There is no common column in both tables.

Source.

Source: Two tables

Product Table

Prod_ID	Prod_Name	Price_USD
2001	Laptop	300
2002	Desktop	200
2003	Mouse	20

 Currency Table

ID	Description	Rate
1	SGR	55
2	INR	62
3	EURO	42

Target Table: 

Prod_ID	Prod_Name	Price_USD	Price_SGR	Price_INR	Price_EURO
2001	Laptop	300	16500	18600	12600
2002	Desktop	200	11000	12400	8400
2003	Mouse	20	1100	1240	840

 
Let's Chat: I know! I know you will be saying hey we can achieve this though joiner transformation easily though we don't have column in both tables. A BIG THANK YOU... You have visited my previous posts and learned it well how to handle.
We all know there will be several way to accomplish business requirement. :-) Here I am opting Active Lookup approach to explain the use. Here we go....

Practically

Step 1: Import Product table. (Import your target table as well.)

Step 2: Expression.
 Create one output port. (This port will be used in condition tab of Lookup)
 SRC_DUMMY_ID (Output Port) = 1

Step 3: Lookup 
a) Create Lookup transformation.... Wait. while creating don't forget to check the option which is shown in Picture below.

b) Add a port manually
   DUMMY_ID (Decimal) Output & Lookup port should be checked.

c) Set condition Tab.

DUMMY_ID = SRC_DUMMY_ID

d) Override the SQL in properties tab.

SELECT CURRENCY.DESCRIPTION as DESCRIPTION, CURRENCY.RATE as RATE, 1 as DUMMY_ID, CURRENCY.ID as ID FROM CURRENCY

Step 4: Aggregator
Connect DESCRPTION, RATE and DUMMY_ID to Aggregator
(All port should be checked as GROUP port)

Step 5: Joiner
a) Create Joiner and Selected Sorted Input in Properties tab.
b) Now Drop all ports from Aggregator and Expression (Step2)
c) Select  Ports from Lookup as Master Port in Port Tab.
d) Condition
DUMMY_ID=SRC_DUMMY_ID

Step 6: Expression
Take Prod_ID, Prod_Name, Price, Description, Rate Port from Joiner.
Create three output port for calculating price.
INR_PRICE (Output Port)=IIF(DESCRIPTION='INR', USD_PRICE*RATE)
SGR_PRICE (Output Port)=IIF(DESCRIPTION='SGR', USD_PRICE*RATE)
EURO_PRICE (Output Port)= IIF(DESCRIPTION='EURO', USD_PRICE*RATE)

Step 7: Aggregator
Connect all ports from expression.
Select PROD_ID port as GROUP port.
Create three output port to de-normalize the data.
O_INR_PRICE (Output Port)=MAX(INR_PRICE)
O_SGR_PRICE (Output Port)=MAX(SGR_PRICE)
O_EURO_PRICE (Output Port)= MAX(EURO_PRICE)

Step 8: Connect to your target.

BINGO!!!

Feel free to post your doubt in comment section or though contact form which you can find below the page.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com