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
Combining record columns into single multiple records
Moderators: chulett, rschirm, roy
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?
Or... something else entirely?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Activities so far
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]
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Combing records solved
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.
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.