I have a requirement in which the input file contains multiple records for same key. I need to find out the sum of an amount field in this file, grouped on key (except for particular record type). If the sum is zeroes, then all the records for that key should be removed.
Eg.
Input file ----
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
MNOP +050.00
MNOP -050.00
MNOP +000.00
MNOP +100.00 OTHER
PQRS +100.00
PQRS +200.00 OTHER
-----------------------------
Sum all records (except of type OTHER). If sum is zeroes, then remove those records (including OTHER). So output should be
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
PQRS +100.00
PQRS +200.00 OTHER
Can someone help me on this? I know that sum can be done in one step and can be checked in another JCL step for removing the records. I would like to know any way this can be done in single step.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
You can use a DFSORT/ICETOOL job like the following to do what you asked for. I assumed your input file has RECFM=FB and LRECL=80, but the job can be changed for other attributes.
Thanks a lot Frank!!! It worked. I was facing unsuccessful sort error and so I changed the INREC BUILD under CTL1 with INREC OVERLAY. It worked fine
In case if I change above requirement such that those keys having zeroes in all records (except OTHER type) should be omitted, is there any chance?
So, if input is
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
MNOP +000.00
MNOP +000.00
MNOP +000.00
MNOP +100.00 OTHER
PQRS +100.00
PQRS +200.00 OTHER
Output expected is:
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
PQRS +100.00
PQRS +200.00 OTHER
Any modifications that would help achieve this? I am trying but all records are not coming.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Quote:
those keys having zeroes in all records (except OTHER type) should be omitted
Quote:
If the sum is zeroes, then all the records for that key should be removed.
Huh? My job already takes care of the case where all the records have zeros. If all of the records have zeros, then the sum is zeros and the records for that key are removed. I tried my job with your example and it removes the MNOP records. Did you try it? Did you get a different result?
Quote:
I was facing unsuccessful sort error and so I changed the INREC BUILD under CTL1 with INREC OVERLAY. It worked fine
I don't know what you mean by this. What sort error? What did you change the INREC statement to? Perhaps that's why you're not getting the result I'm getting. You need to show me the error you received with my original INREC statement and what you changed it to.
Yes Frank, you example do remove key records where all records under that key has zeroes. This is because sum of all these records will be zero and hence the job will take care of this. However, consider this case.
ABCD +100.55
ABCD +055.96
ABCD +666.33 OTHER
MNOP +000.00
MNOP +000.00
MNOP +000.00
MNOP +100.00 OTHER
PQRS +100.00
PQRS -100.00
PQRS +000.00 OTHER
Output expected is:
ABCD +100.55
ABCD +055.96
ABCD +963.65
ABCD +666.33 OTHER
PQRS +100.00
PQRS -100.00
PQRS +000.00 OTHER
Please note that the above job will remove MNOP and PQRS (since sum of both these records yield zeroes). However, we need to remove only those where every record is having zero value in it (like MNOP). In PQRS, both the records have non-zero (even though sum is zeroes). Hence, it should not be removed.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Oh, I guess this is a new requirement where you want to omit records that have all zeros rather than records that sum to zero (including those that have all zeros). Here's the DFSORT/ICETOOL job for that new requirement.
