Concatenation

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
kvenna
Participant
Posts: 1
Joined: Thu May 18, 2017 4:18 am
Location: mumbai

Concatenation

Post by kvenna »

Hi,
I have Multiple records with some having same key ID.I want to concatenate them
eg input:
ID Col
1 a
1 b
1 c
1 d
2 d
2 e
2 g
3 e
3 s
3 f


And the output should be like this.

output
ID Col
1 a,b,c,d
2 d,e,g
3 e,s,f

anyone who know the solution please help me with this.

Thanks in advance.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

That is a vertical pivot (rows to columns) but with... a twist. Rather than use a pivot stage, you'll need to do it yourself in a transformer. Have you ever worked with stage variables to do "group change detection"? In your example, that would let you know when you hit a new ID value. Easy enough to do it in the transformer or you can leverage a Sort stage (even if it doesn't sort) to add a change key for you automatically, then in the transformer you just look for it rather than derive it.

Then do your concatenation. First record of a group? Put it in your output column. Any other record in the same group gets concatenated to the output column with a comma in front of it. Then only output the last record in each group to your target.

There are environment variables that can help with that or you can use an Aggregator and the Last() aggregate function after grouping on ID.

A quick search turned up this post (one of many) on the details of group change detection.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

The LastRowInGroup() transform function eliminates the need for an aggregator after the transformer. This transform function requires an upstream sort stage (even if it is set to no sort).

Mike
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Ah, yes - thanks Mike. Had a bit of a <squirrel!> moment and didn't add that in as intended after my search. :D
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply