DSXchange

Posted: **Wed Aug 23, 2006 10:48 am**

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

Posted: **Wed Aug 23, 2006 12:09 pm**

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

Posted: **Wed Aug 23, 2006 12:13 pm**

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

Posted: **Wed Aug 23, 2006 1:20 pm**

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

Posted: **Wed Aug 23, 2006 1:38 pm**

Hi all,

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

Bestest!

Posted: **Wed Aug 23, 2006 1:42 pm**

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

Posted: **Wed Aug 23, 2006 2:43 pm**

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.

Posted: **Wed Aug 23, 2006 3:02 pm**

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

Will get back to you guys again...

Posted: **Wed Aug 23, 2006 3:40 pm**

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.

Posted: **Wed Aug 23, 2006 4:50 pm**

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

Posted: **Wed Aug 23, 2006 6:16 pm**

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

Posted: **Thu Aug 24, 2006 12:53 am**

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

DSXchange

String aggregation

String aggregation

What about the Pivot Stage for this?