DSXchange

I've been investigating how to do break processing within DataStage but have had no luck... here is what I need to do...

From a single input source I need to group rows together by 3 key values (not a problem) then I need to assign an incrementing number starting from 1 and continuing to n based upon the number of rows the 3 key values present... so if keys 1-3 are identical for 5 records, I need to assign the first one with a '1', the 2nd with a '2', and so on... and for each gouping of the 3 keys, I need to start with '1' again...

I've looked at sequence generator, aggregator, stage variables, etc... and have not figured out how to maintain the previous values, or set up a variable 'counter' that I can reset to '1' each time I get new key values...

Am I missing something in DS???... does a stage exist that can do this???... or has anyone had a similar situation and had to do a workaround...

Any help would be appreciated...

This can be done in a transform using 4 stage variables:

GroupCounter = IF Key1=LastKey1 AND Key2 = LastKey2 AND Key3 = LastKey3 THEN GroupCounter+1 ELSE 1
LastKey1 = Key1
LastKey2 = Key2
LastKey3 = Key3

I would just use two, one for the counter and one for the previous 'composite' key. Of course, this presupposes the data has been sorted. Note that having the Sort stage add a Key Change column can make this a little easier.

Craig caught that:

Should we consider the partition method here if the input is not a sequential file? Should the input link need to be partitioned in round robin method?

Data should be partitioned on the sort key.

Thanks to you all! It appears I have it working the way I want.

Thanks again.