joining the common rows

[us1aslam1us]

Hi all,

I am having an input:

name,add,loc,pos
A,l,s,BA
A,l,s,CA
A,l,s,DA

i need to transform it to

name,add,loc,pos
A,l,s,BA/CA/DA

I tried to use stage variables but any help is appreciated.

Thanks
Sam

[vmcburney]

Stage variables will do the trick as long as the data is sorted and partitioned by the key columns A,I,s. Stage variables can hold values from previous rows as long as you fire them in the right order.

Something like this:
NewKey=input.name:|:input.add:|:input.loc
Separator=IF NewPos<>"" THEN "/" ELSE ""
NewPos=IF NewKey=OldKey THEN Pos:Separator:input.pos ELSE ""
OldKey=NewKey

[us1aslam1us]

Thanks for the response vincent but it had been resolved way back with the same solution you provided.