Look-up On Multiple Records In Informatica

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

Pivoting of records(Pivoting of Employees by Department)

In this scenario we will discuss about how to pivot a set of records based on column:

Source Records: 

Dept_id	Emp_name
10	CLARK
10	KING
10	MILLER
20	JONES
20	FORD
20	ADAMS
20	SMITH
20	SCOTT
30	WARD
30	TURNER
30	ALLEN
30	BLAKE
30	MARTIN
30	JAMES

Expected Output

DEPT_ID	EMP_NAME
10	CLARK|KING|MILLER
20	JONES|FORD|ADAMS|SMITH|SCOTT
30	WARD|TURNER|ALLEN|JAMES|BLAKE|MARTIN

Look-up On Multiple Records In Informatica

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