Concatenate multirow data into one line
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 7
- Joined: Wed Feb 06, 2013 11:31 am
Concatenate multirow data into one line
Hi,
input file:
CLASS, STUDENTS
--------------------------
1,Ram
1,Hari
1,Raj
2,John
2,terry
2,Smith
2,Peter
O/p should be:
CLASS, STUDENTS
--------------------------
1,RamHariRaj
2,JohnterrySmithPeter
I wanted to concatenate all the rows based on the group column which is class here.
Please suggest the possible solution here.
Thanks in Advance.
input file:
CLASS, STUDENTS
--------------------------
1,Ram
1,Hari
1,Raj
2,John
2,terry
2,Smith
2,Peter
O/p should be:
CLASS, STUDENTS
--------------------------
1,RamHariRaj
2,JohnterrySmithPeter
I wanted to concatenate all the rows based on the group column which is class here.
Please suggest the possible solution here.
Thanks in Advance.
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Where is the interview? ![Wink :wink:](./images/smilies/icon_wink.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
-
- Participant
- Posts: 7
- Joined: Wed Feb 06, 2013 11:31 am
I tried the below method in 9.1..
SRC-->SORT-->TFM-->Remove Duplicate--> TGT
Sort by class and in TFM, I am taking 3 stage variables as below order
KeyNext=class
concatText=
If keyNext <> keyPre Then STUDENTS else concatText: STUDENTS
keyPre=KeyNext
Here I am getting partial o/p as the first row data (Ram) is not going to the target.
Hope this will help..
SRC-->SORT-->TFM-->Remove Duplicate--> TGT
Sort by class and in TFM, I am taking 3 stage variables as below order
KeyNext=class
concatText=
If keyNext <> keyPre Then STUDENTS else concatText: STUDENTS
keyPre=KeyNext
Here I am getting partial o/p as the first row data (Ram) is not going to the target.
Hope this will help..
-
- Participant
- Posts: 7
- Joined: Wed Feb 06, 2013 11:31 am
If you are running on multiple nodes, how are you paritioning? And if so, does it work properly if you run on a single node? You could also cut this back to one stage variable if you let the Sort stage add a Key Change column. You could also leverage Transformer Looping. FYI.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Reply
Add transformer with following partition.
class partition hash sort ascending, student no partition sort only ascending.
transformer stage variables :
student : if input.class=classold then studentold:input.student else input.student
classold : input.class
studentold : student
Then add remove duplicate stage with key = class and duplicate to retain last
Partition : same
class partition hash sort ascending, student no partition sort only ascending.
transformer stage variables :
student : if input.class=classold then studentold:input.student else input.student
classold : input.class
studentold : student
Then add remove duplicate stage with key = class and duplicate to retain last
Partition : same
Or just add a KeyChange column over Class in the Sort stage. Then just one stage variable will do:
Then just make sure your partitioning is correct or run on a single node. And keep the last duplicate. ![Wink :wink:](./images/smilies/icon_wink.gif)
Code: Select all
svStudents: If KeyChange then link.student else svStudents : link.student
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers