String aggregation

[urshit_1983]

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

[Krazykoolrohit]

Sort the data stream on Custname.

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

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

[DSguru2B]

I think even the row merger can take care of your problem. Look into the row merger stage.

[Krazykoolrohit]

But how will we concatenate different rows in row merger concatenating one column but leaving the other intact?

[jdmiceli]

Hi all,

Wouldn't the pivot stage work in this case as well?

Bestest!

[Krazykoolrohit]

Just post in all the possible solutions so that our dear friend gets all options before him.

[DSguru2B]

I am not exactly sure. I do not have access to datastage at the moment. But it looked as if it was possible by row merger. I have never used that stage personally, just played around with it once.

[urshit_1983]

the variable method by rohit is not working. I have not tried the other two methods.

Will get back to you guys again...

[urshit_1983]

DSGuru I tried Row merger but getting confused. I am getting strange results. I have also never used this stage before so a bit ignorant on this. Your help would be appreciated.

[us1aslam1us]

You can try this :

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

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

[DSguru2B]

I did something similar a while back. Check out thispost.

[ray.wurlod]

It's a vertical pivot. Search the forum for this term. At least three techniques have been published here.