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
- 9113 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / gd
Use aggregator t/r In that condition group by on emp_id and
create output port with expression concat('emp_id','
','emp_name');
| Is This Answer Correct ? | 5 Yes | 0 No |
SOURCE->SRT->EXP->AGT->TGT
SRT - Sort by column col1.
EXP - empid
name
v_name=iif(v_empid=empid,v_name||','||name,name)
v_empid=empid
o_empid=v_empid
o_name=v_name
After this we can use aggregator t/r to take last row for a
particualr emp id using LAST.
| Is This Answer Correct ? | 4 Yes | 0 No |
Answer / nanda
In expression transformation take a variable port, lets say
v_emp_id and equate it to emp_id,
take one more variable port for calculation as v_cal
and write a expression in v_cal as
v_cal= iff(emp_id=v_emp_id, emp_name||v_cal, emp_name)-----
----- (1)
take an output port O_emp_name and
O_emp_name = v_cal
write the following as shown in the expression
transformation, i meant the order of ports and their
corresponding values
emp_id
emp_name
v_cal the (1) expression
v_emp_id emp_id
O_emp_name v_cal
send the output ports to aggregator and in aggregator
take a output port O_agg_emp_name
emp_id
emp_name
O_emp_name
O_agg_emp_name = last(O_emp_name)
send the emp_id and the O_agg_emp_name to the target
and thts it
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / 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 |
Answer / shirish
In expression transformation use an output port ...in
expression window write emp_id||empname
| Is This Answer Correct ? | 7 Yes | 15 No |
can every transfomation reusable?how?
If source is having 5000 rows,and if you want to load data into 500 rows to target,if the session runs successfully,how can you verify the target if the rows are successfully loaded or not.
How to eliminate 1st and last rows from the source and load the inbetween rows.
Can any one give me an example for factless fact table ? If your answer is studunt attendence registration,could you plese give me explanation for this ?
3 Answers Cap Gemini, Puma, Wipro,
how to duplicates from expression transformation without using sorter before that
is there any way to read the ms excel datas directly into informatica?like is there any possibilities to take excel file as target?
In operator is used in which transformation in informatica 8.6.0
From where you extract the data, how you did it into informatica? explain....
1 Answers Accenture, Unisoft Infotech,
How many repositories can we create in Informatica?
How do you manage the Parameter files while migrating your data from one environment to another environment?
how to get flatfile containg 10 records half records one target another half another target
How will u pas the data with out debugger?
Teradata 
