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.
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
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