Interested to Buy Any Domain ? << Click Here >> for more details...
I have a flat file, in which i have two fields, emp_id,
emp_name. The data is like this,
emp_id,emp_name
101,soha
101,ali
101,khan
102,siva
102,shanker
102,reddy.
how to merge the names so that my output is like this
Emp_id Emp_name
101 Soha ali kahn
102 siva shenkar reddy
please provide solution
- 5 Answers
- 11147 Views
- I also Faced
- E-Mail Answers
Answer Posted / rana
In Expression transformation create four variable port like:
v_ID
v_Fname
v_Mname
v_Lname
and use following condition
v_nr=iif(v_id <> emp_ID,0,v_nr+1)
v_fname=iif(v_nr = 0,emp_name,v_fname)
v_mname=iif(v_nr = 1,emp_name,v_mname)
v_lname=iif(v_nr = 2,emp_name,v_lname)
v_id=iif(v_id <> emp_ID,emp_id,v_id)
Add a Filter transformation, and condition will be (NOT
ISNULL(v_Fname) and NOT ISNULL(v_Mname) and NOT ISNULL
(v_Lname))
Take an example, If rows are like these:
101,soha
101,ali
101,khan
102,siva
102,shanker
102,reddy
For : 101,soha ==>
*v_nr=iif(v_id <> 101,0,v_nr+1) [v_id is now 0 or
null]
It will return 0
*v_fname=iif(v_nr = 0,emp_name,v_fname) [v_nr is 0 now
so "SOHA" will insert into f_name variable]
other condition will be false
-------------------------------
For : 101,ali ==>
*v_nr=iif(101 <> 101,0,v_nr+1) [v_nr is now 1 ]
It will return 1, because condition will FALSE and v_nr+1
will add 1 to 0, mean 1
*v_fname=iif(v_nr = 0,emp_name,v_fname) [v_nr is 1 so
condition is FALSE and it will return v_fname that
is "SOHA" that was stored when v_nr was 0]
*v_mname=iif(v_nr = 1,emp_name,v_mname) [v_nr is 1 and
Condition is TRUE so now it will return emp_name and that
is "ALI"]
other condition will be false
---------------------------------
For : 101,khan ==>
*v_nr=iif(101 <> 101,0,v_nr+1) [v_nr is now 2]
It will return 2, because condition will FALSE and v_nr+1
will add 1 to 1, mean 2
*v_fname=iif(v_nr = 0,emp_name,v_fname) [v_nr is 2 so
condition is FALSE and it will return v_fname that
is "SOHA" that was stored when v_nr was 0]
*v_mname=iif(v_nr = 1,emp_name,v_mname) [v_nr is 2 and
Condition is FALSE and it will return v_mname that is "ALI"
that was stored when v_nr was 1]
*v_lname=iif(v_nr = 2,emp_name,v_lname) [v_nr is 2 now so
condition is TRUE and it will return emp_name and that
is “KHAN” now]
v_id=iif(v_id <> emp_ID,emp_id,v_id)
Let me know if you have still any confusion on this,
Rana
| Is This Answer Correct ? | 4 Yes | 4 No |
Post New Answer View All Answers
can any one give some examples for pre sql and post sql(Except dropping and creating index).
I need the output file in the below format using Informatica. __0946684800__ 6 1 3 1 5 1 6 1 10 1 6 10 Pos State Time Split AgentID Reason Spaces Code in Code Spare state ______ ___ _____ ______ __________ ______ 1234 14 0 4321 3103 abcd 4321 15 5 123452 3150 Can someone help please?? We need the data in space-delimited, fixed ASCII format .
Explain in detail about scd type 1 through mapping.
What are the modules in Power Center
what are the different types of transformation available in informatica. And what are the mostly used ones among them?
How many ways are there to do 'remove duplicate records in informatica'?
What is meant by query override?
Write the program through which the records can be updated?
What is workflow? What are the components of the workflow manager?
What are different types of transformations available in informatica?
What are the differences between joiner transformation and source qualifier transformation?
where to store informatica rejected data?
What is informatica metadata and where is it stored?
What is joiner change?
How to extract sap data using informatica? What is abap? What are idocs?
