Help with Logic - How to split the records into groups

DSFreddie · Post by **DSFreddie** » Mon Jul 16, 2012 8:40 am

Hi All,

I am coding for a requirement where I need to split the input records based on the no:of records with the same value for a Field A. When the no: of records with the same key value exceeds 6, then it needs to split into 2 groups & assign a new group value.

That is, if the no: of records with the same key field A is more than 6, the whole record group should be split into multiple groups.

e.gs:

if the no: of records with the same key field A is 6, then 6 (Same Field A value)

if the no: of records with the same key field A is 7, then 5 +2 (Assign a new value for Field A for the 2 recs)

if the no: of records with the same key field A is 8, then 5+3 (Assign a new value for Field A for the 3 recs)

if the no: of records with the same key field A is 10, then 5+5 (Assign a new value for Field A for the new 5 recs)

if the no: of records with the same key field A is 11, then 5+6 (Assign a new value for Field A for the 6 recs)

If the no: of records with the same key field A is 12, then 5+5+2 (Assign new values for Field A for the new 5 & 2 recs)

Can somebody help me by sharing some thoughts on how to accomplish this in DataStage.

Thanks Much
Freddie

jwiles · Post by **jwiles** » Mon Jul 16, 2012 9:00 am

Is your job responsible for assigning the value for Field A?

Simple counter logic using stage variables within a transformer is all that you need. If the assignment is across ALL records, then something like this may work (and may require that you run the transformer sequentially depending upon your requirements):

svCounter (initial value 0): svCounter+1
svFieldA (whatever initial value you require): if mod(svCounter,5) = 0 then svFieldA+1 else svFieldA

If you need to assign FieldA within a key value (records grouped by another key column), a variation of the above to recognize the change in key value would be required. If you are using IS 8.5 or 8.7, you can use some of it's grouping/looping capabilities to make this a little easier.

Regards,

ankursaxena.2003 · Post by **ankursaxena.2003** » Mon Jul 16, 2012 9:42 am

In order to check the value between 2 consecutive records, use Sort Stage.
In Sort Stage there is an option of "Create Key Change Column", set it true. So, it will change to '1' whenever you have change in the Key field.

Key Field KeyChange
1 1
1 0
1 0
1 0
2 1
2 0
3 1
4 1