String aggregation
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
String aggregation
I am finding it hard to find out whether the following thing can be done in DS ??
Input is like :-
Custname--------------Custid
Mel --------------- 001
Dino----------------------002
Mel------------------------007
Dino-----------------------009
Jack------------------------100
Jack------------------------200
Outout I want is like:-
Custname------------------Custid
Mel--------------------------001,007
Dino-------------------------002,009
Jack-------------------------100,200
(----) is just to separate two columns.
Thanks
Input is like :-
Custname--------------Custid
Mel --------------- 001
Dino----------------------002
Mel------------------------007
Dino-----------------------009
Jack------------------------100
Jack------------------------200
Outout I want is like:-
Custname------------------Custid
Mel--------------------------001,007
Dino-------------------------002,009
Jack-------------------------100,200
(----) is just to separate two columns.
Thanks
"Nobody is expert in Everything,
But Everybody is expert in Something."
But Everybody is expert in Something."
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Sort the data stream on Custname.
in the transformer add three stage variables Var1, Var2 and Var3.
I hope you understood the logic.
Use Var3 in the output link. also add a constraint to the link (pass rows only where Customername!=Var3 as:
Var1=1
in the transformer add three stage variables Var1, Var2 and Var3.
Code: Select all
Derivation of Var1:
If Custname=Var3 then 0 else 1
Derivation of Var2:
If Custname=Var3 then Custid:',':Var2 Else ' '
Derivation of Var3:
If Custname=Var3 then Var3 Else Custname
Use Var3 in the output link. also add a constraint to the link (pass rows only where Customername!=Var3 as:
Var1=1
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
What about the Pivot Stage for this?
Hi all,
Wouldn't the pivot stage work in this case as well?
Bestest!
Wouldn't the pivot stage work in this case as well?
Bestest!
Bestest!
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
John Miceli
System Specialist, MCP, MCDBA
Berkley Technology Services
"Good Morning. This is God. I will be handling all your problems today. I will not need your help. So have a great day!"
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
You can try this :
After transformer use remove duplicate stage for removing the duplicates and retain the last rows for each set.
May be guru can help you in tweaking this .
Sam
Code: Select all
VarResult=> If Custname=Var1 then VarCustid:',':Custid else Custid
Var2=> Var1
Var1=> A
In the output columns define
Custname=>Custname
Custid=>VarResult
May be guru can help you in tweaking this .
Sam
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: