Lookup On Multiple Records In Informatica 9

Informatica 9 Scenario: Need to lookup on File with multiple records and extract records with specific condition (For example in this case only account number starting with “12”)

 Source File

ID	User_Name
1	James
2	Williams
3	Ravi

Lookup file

ID	Account_Name	Account_number
1	Acc _James_1	123232
1	Acc _James_2	45778266
2	Acc _ Williams_1	5455546
2	Acc _ Williams_2	1234343
3	Acc _ Ravi_1	254589
3	Acc _ Ravi_2	12544456

Expected Output

ID	Account_Name	Account_number	User_Name
1	Acc _James_1	123232	James
2	Acc _ Williams_2	1234343	Williams
3	Acc _ Ravi_2	12544456	Ravi

a)In the Lookup condition give the option for Multiple Output for the matching records (this option only available for Informatica 9).The output in Lookup will be as below(Data in Expression)

ID	Account_Name	Account_number	User_Name
1	Acc _James_1	123232	James
1	Acc _James_2	45778266	James
2	Acc _ Williams_1	5455546	Williams
2	Acc _ Williams_2	1234343	Williams
3	Acc _ Ravi_1	254589	Ravi
3	Acc _ Ravi_2	12544456	Ravi

b)In expression check for the account starting with “12” using below condition

IIF (SUBSTR (Account_number, 1, 1) =’12’, 1, 0)

 

c)Next step is quite simple. We can use a filter and take records only were the flag is 1.The output will be as below

ID	Account_Name	Account_number	User_Name
1	Acc _James_1	123232	James
2	Acc_ Williams_2	1234343	Williams
3	Acc _ Ravi_2	12544456	Ravi