SUM and count of records by key, using SORT utility



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

SUM and count of records by key, using SORT utility

Postby naveenkumar sudha » Mon May 09, 2022 10:11 pm

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:

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
naveenkumar sudha
 
Posts: 21
Joined: Mon Nov 23, 2009 10:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: SUM and count of records by key, using SORT utility

Postby sergeyken » Mon May 09, 2022 11:02 pm

0. Please, learn how to use the Code button to format your code properly

1. In case you are using DFSORT utility, please read SECTIONS part of the manual (Page 339). No plans do exist to copy parts of the manual to the forum; it can be read from its original location.

2. After reading the manual, try to create a working code, and present it to the forum with your technical questions.

3. Do not create clones of the same topic; ask administrators to move it to the correct forum section.
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 278
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 25 times

Re: SUM and count of records by key, using SORT utility

Postby ssbylzh » Sat May 21, 2022 5:10 pm

Try to use below:

INREC BUILD=(1,35,C'|1')
SORT FIELDS=(1,31,CH,A)
SUM FIELDS=(32,4,ZD,37,1,ZD)
 
ssbylzh
 
Posts: 1
Joined: Sat May 21, 2022 4:56 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post