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
Help with Logic - How to split the records into groups
Moderators: chulett, rschirm, roy
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 96
- Joined: Mon May 14, 2012 1:30 pm