Combining record columns into single multiple records

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
wpakkala
Participant
Posts: 7
Joined: Sun Jul 17, 2011 7:13 am

Combining record columns into single multiple records

Post by wpakkala »

Here is my data
key seq field
1 1 f1
1 2 f2
1 3 f3
2 1 ff1
2 2 ff2

I want to get the following output

key field
1 f1,f2,f3
2 ff1,ff2

Looking for some assistance as to how to perform this in DataStage 11.5
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I for one would be curious what you've tried so far. Did any of the things you've tried involve concatenating into a stage variable and a Remove Duplicates stage, perhaps? Or sorting and a KeyChange column?

Or... something else entirely?

:wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
wpakkala
Participant
Posts: 7
Joined: Sun Jul 17, 2011 7:13 am

Activities so far

Post by wpakkala »

I have tried using a sort/transformer but then realized that this will only create a single record.....I am interested in multiple records.....[also, I did not get the results I wanted but this is my error]
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

This can be easily solved using Transformer stage looping, using EndOfGroup() processing to detect the end of each key value (make sure you also partition by the key value).

Build the comma-delimited string using loop variables, and transfer to output only when the end of each group is reached.

There is an almost identical example in the IBM documentation, and in the IBM training materials.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
wpakkala
Participant
Posts: 7
Joined: Sun Jul 17, 2011 7:13 am

Combing records solved

Post by wpakkala »

I have solved the issue:

Using a Transformer Stage,

I created a Stage Variable to concatenate the columns I want together. This is based on the ClusterChangeKey set up by a sort stage just previous.

The other key point was to use a LastRowInGroup() as a constraint on the output so that I only get 1 record output when all of the rows that belong together are combined.

Thanx for the responses.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

As with most things DataStage, there are multiple ways to solve this. :wink:
-craig

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