Hi Team,
I have an FB file as below, consisting of 5 fields - A,B,C,D(YEARMM),E(Amount), delimited by pipe symbol
I need to sum-up amounts and count no of records, for all matching A,B,C,D(YEARMM) and write to a new file having fields- A,B,C,D(YEARMM), accumulated/summarized AMOUNT and no of records having same A,B,C,D.
I/P:
----+----1----+----2----+----3----+----4----+----5----+----
C1AW|1013300 |16L00000 |202201|0001
C1AW|1013300 |16L00000 |202202|0001
C1AW|1013300 |16L00000 |202202|0001
C1AW|1020000 |16L21000 |202112|0001
C1AW|1020000 |16000000 |202103|0001
C1AW|1020000 |16000000 |202103|0002
C1AW|1020000 |16000000 |202106|0001
O/P Expected:
-----------------
C1AW|1013300 |16L00000 |202201|0001|1
C1AW|1013300 |16L00000 |202202|0002|2
C1AW|1020000 |16L21000 |202112|0001|1
C1AW|1020000 |16000000 |202103|0003|2
C1AW|1020000 |16000000 |202106|0001| 1
I can sum-up amounts based on column:A,B,C,D using SUM FIELDS=NONE and use M10 function to count records, having individual steps. But again, i need to merge these individual steps which is inefficient.
Hence i would need your help, can this be done in single step - both summing up and writing count of records based on matched key.
Kindly help.
Thanks a lot,
Naveen S