Posted: Mon Nov 13, 2017 12:55 pm Post subject: Summing up a particular group of records into a single row?
Need help with summing up a particular group of records into a single row.
Description:
Output file is created from a mainframe report thru sort card, my requirement is to sum the rows with T52, T61 On column 64 into a single row
We tried the below Sort code, but it was populating zero for other rows which have to be empty.
Input:
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
72 NOVEMBER 27 2017 SOR T44
101,111 NOVEMBER 27 2017 SOR T46
688,616 NOVEMBER 27 2017 SOR T45
34,433,316,245 NOVEMBER 27 2017 SOR T47
6,666 NOVEMBER 27 2017 SOR T48
66,666,663 NOVEMBER 27 2017 SOR T49
5,421 NOVEMBER 27 2017 SOR T50
2,344 NOVEMBER 27 2017 SOR T51
685 NOVEMBER 27 2017 SOR T52
87 NOVEMBER 27 2017 SOR T52
4 NOVEMBER 27 2017 SOR T52
31 NOVEMBER 27 2017 SOR T53
777,777 NOVEMBER 27 2017 SOR T55
678,987 NOVEMBER 27 2017 SOR T54
9,999,999,999 NOVEMBER 27 2017 SOR T56
449 NOVEMBER 27 2017 SOR T57
41,194,660 NOVEMBER 27 2017 SOR T58
1,111 NOVEMBER 27 2017 SOR T59
444 NOVEMBER 27 2017 SOR T60
111 NOVEMBER 27 2017 SOR T61
70 NOVEMBER 27 2017 SOR T61
0 NOVEMBER 27 2017 SOR T61
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
72 NOVEMBER 27 2017 SOR T44
101,111 NOVEMBER 27 2017 SOR T46
688,616 NOVEMBER 27 2017 SOR T45
34,433,316,245 NOVEMBER 27 2017 SOR T47
6,666 NOVEMBER 27 2017 SOR T48
66,666,663 NOVEMBER 27 2017 SOR T49
5,421 NOVEMBER 27 2017 SOR T50
2,344 NOVEMBER 27 2017 SOR T51
776 NOVEMBER 27 2017 SOR T52
31 NOVEMBER 27 2017 SOR T53
777,777 NOVEMBER 27 2017 SOR T55
678,987 NOVEMBER 27 2017 SOR T54
9,999,999,999 NOVEMBER 27 2017 SOR T56
449 NOVEMBER 27 2017 SOR T57
41,194,660 NOVEMBER 27 2017 SOR T58
1,111 NOVEMBER 27 2017 SOR T59
444 NOVEMBER 27 2017 SOR T60
181 NOVEMBER 27 2017 SOR T61
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Mon Nov 13, 2017 4:32 pm Post subject:
rgcmohan,
Replace the T in the edit mask with I if you don't want to have zeroes printed out. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Incase we have all zeroes for T61, we need the zero displayed,
We need to sum up the values in columns 10,7 ,only for the rows appearing thrice
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
72 NOVEMBER 27 2017 SOR T44
101,111 NOVEMBER 27 2017 SOR T46
688,616 NOVEMBER 27 2017 SOR T45
34,433,316,245 NOVEMBER 27 2017 SOR T47
6,666 NOVEMBER 27 2017 SOR T48
66,666,663 NOVEMBER 27 2017 SOR T49
5,421 NOVEMBER 27 2017 SOR T50
2,344 NOVEMBER 27 2017 SOR T51
685 NOVEMBER 27 2017 SOR T52
87 NOVEMBER 27 2017 SOR T52
4 NOVEMBER 27 2017 SOR T52
31 NOVEMBER 27 2017 SOR T53
777,777 NOVEMBER 27 2017 SOR T55
678,987 NOVEMBER 27 2017 SOR T54
9,999,999,999 NOVEMBER 27 2017 SOR T56
449 NOVEMBER 27 2017 SOR T57
41,194,660 NOVEMBER 27 2017 SOR T58
1,111 NOVEMBER 27 2017 SOR T59
444 NOVEMBER 27 2017 SOR T60
0 NOVEMBER 27 2017 SOR T61
0 NOVEMBER 27 2017 SOR T61
0 NOVEMBER 27 2017 SOR T61
There is no chance of duplicates, since the input is created from Sort step out of mainframe report and we create 3 instances for T52, T61 and other rows will have only one instance . We need to sum columns(10,7) which has an value >= zero.
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Wed Nov 15, 2017 11:53 am Post subject:
Suchay wrote:
Kolusu,
There is no chance of duplicates, since the input is created from Sort step out of mainframe report and we create 3 instances for T52, T61 and other rows will have only one instance . We need to sum columns(10,7) which has an value >= zero.
Regards,
Suchay
Why can't the summation be done in the same sort step? Why do you need another step to create sum the 3 instances? _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Why do you need another step to create sum the 3 instances?
We need to pick these three rows 91-120, 121-150,OVER-150 and
total the count,amount, so we created each row with SOR T16, so we could sum it up in the next step
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Wed Nov 15, 2017 3:44 pm Post subject:
Suchay wrote:
Same build would be repeated but SOR T would differ for each newyor
Suchay,
Why are you complicating a simple request? Why do you need to change the SORT card for every year, you can process ALL years or a Single year without ever bothering to change the sort card.
The input you show does not truly match the control cards you have. Show me the original input file.
Do you need the intermediate file that you show in this topic or go straight to the summation of 3 records? _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Why are you complicating a simple request? Why do you need to change the SORT card for every year, you can process ALL years or a Single year without ever bothering to change the sort card.
It is the regions not the year (01, 02, 03, 04, 05, 06, 07 ) and it is single sort card , we get the report monthly we need to extract the records for each region and each region has it own set of SOR T**.
I have provided region 01 since the sort card is long.
Quote:
Do you need the intermediate file that you show in this topic or go straight to the summation of 3 records?
if we can sum it up the 3 records straight, it would be good
Quote:
The input you show does not truly match the control cards you have. Show me the original input file.
It is an report so I edited the records to be summed up, below is the one for Newyork 01 and last page for total of all regions
Code:
Count Amount aMOUNT1 SELEC2 READY RRRR YYYYY RR XX MM
------- -------------- -------------- -------------- -------------- ----- ------ --------
rrrrrrrrrrrr 666,666 11,111,111,111 3,333,333,333 11,111,111,111 10,101,010,108 .86 1.11 16 26 20
MONmoN MON 72,222,222 72,222,222
NEW NEW NEW 7,536 197,037,905 197,037,905 114,961,261 .77 1.57 36 1 35
RRRRRRRRR 19 505,849 7,420 498,429 289,452 .67 1.32 36 1 35
TTTTTTTTTTTT 5,783 149,524,212 52,722,101 96,802,111 89,032,370 .96 1.92 36 32 4
RERRRRRRRRRR
PAYPPPPPP 1,548 43,465,416 14,004,334 29,461,082 25,428,791 1.54 2.32 37 29 7
TRTRTRTRTR IN
TRTRTRTRTR OUT
EEEEEE PORTFOLIO 689,495 17,987,966,563 3,347,942,436 14,640,024,127 10,806,680,966 .86 1.71 36 17 19
0 ------------------------------------------- TOTAL P ------------------------------------------
0
UNITS BOOK SELEC2 bbbbbbbb BOOK cOUNT aMT Yld
----------- ---------------- ---------- --------------- ------- --------------- -------
TOTAL TTTTTTTTTTT 2,860 55,469,811 703,301 14,909,981,850 1.78
TOTAL CCCCCCCCC LLLLLL
----------- ---------------- ---------- --------------- ------- --------------- -------
TOTAL PXXXXXXXXX 2,860 55,469,811 703,301 14,909,981,850 1.78
1REPT01 Report main Header PAGE 176
0FOR: NOVEMBER 10 2017 Summary Report NOVEMBER 10 2017 20:39:55
0NEWYORK:01
0---------------------------------------------------- aDMIN ------------------------------------------------------
MTD MTD YTD
coUNT AMOUNT AMOUNT UNITS UNEAR IN PROCESS LTD EAR coUNT coUNT
------- ------------ ------------ ------- ------------ ------------ ------------ ------ ------
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Thu Nov 16, 2017 10:26 am Post subject:
Suchay wrote:
Kolusu,
It is the regions not the year (01, 02, 03, 04, 05, 06, 07 ) and it is single sort card , we get the report monthly we need to extract the records for each region and each region has it own set of SOR T**.
I have provided region 01 since the sort card is long.
Suchay,
It does not matter whether it is a region or year as you can dynamically pass parms via JPn format and with a simple Change command you can populate the different Tnn values based on the region.
Just for your info whoever wrote that sort card has a round about way of doing things. They are wasting a lot of resources. There are so many gross errors that makes no sense. For example the WHEN=GROUP for SELEC1 and SELEC2 and you push 5 bytes(SELEC) but in the OUTFIL you check for SELEC1 and SELEC2. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
IFTHEN=(WHEN=(151,2,CH,EQ,C'01',AND,
36,8,CH,EQ,C'OVER-150'),
BUILD=(9X,
47,07,UFF,EDIT=(III,IIT),
17X,
134,17, $ DATE
9X,
C'SOR T16',
67X)),
IFTHEN=(WHEN=(151,2,CH,EQ,C'AN',
AND,2,5,CH,EQ,C'NEW N'),
BUILD=(18X,
31,14,UFF,EDIT=(II,III,III,IIT), $ UNITS
1X,
134,17, $ DATE
9X,
C'SOR T4',
68X)),
IFTHEN=(WHEN=(151,2,CH,EQ,C'AN',AND,
2,5,CH,EQ,C'REVER'),
BUILD=(9X,
22,07,UFF,EDIT=(III,IIT), $ UNITS
17X,
134,17, $ DATE
9X,
C'SOR T2',
68X,/,
18X,
31,14,UFF,EDIT=(II,III,III,IIT), $ AMT
1X,
134,17, $ DATE
9X,
C'SOR T5',
68X)),
IFTHEN=(WHEN=(151,2,CH,EQ,C'AN',AND,
2,5,CH,EQ,C'TERMI'),
BUILD=(18X,
31,14,UFF,EDIT=(II,III,III,IIT), $ AMT
1X,
134,17, $ DATE
9X,
C'SOR T6',
68X)),
IFTHEN=(WHEN=(151,2,CH,EQ,C'AN',AND,
2,5,CH,EQ,C'PAYED'),
BUILD=(18X,
31,14,UFF,EDIT=(II,III,III,IIT), $ AMT
1X,
134,17, $ DATE
9X,
C'SOR T7',
68X)),
IFTHEN=(WHEN=(151,2,CH,EQ,C'AN',AND,
2,5,CH,EQ,C'EEEEE'),
BUILD=(9X,
22,07,UFF,EDIT=(III,IIT), $ UNITS
17X,
134,17, $ DATE
9X,
C'SOR T3',
68X))
Output produced out of the above sortcard
Code:
***************************** Top of Data *****************************
19 NOVEMBER 10 2017 SOR T8
498,429 NOVEMBER 10 2017 SOR T10
689,495 NOVEMBER 10 2017 SOR T9
14,640,024,127 NOVEMBER 10 2017 SOR T11
2,860 NOVEMBER 10 2017 SOR T12
55,469,811 NOVEMBER 10 2017 SOR T13
8,161 NOVEMBER 10 2017 SOR T14
9,999 NOVEMBER 10 2017 SOR T15
511 NOVEMBER 10 2017 SOR T16
63 NOVEMBER 10 2017 SOR T16
7 NOVEMBER 10 2017 SOR T16
197,037,905 NOVEMBER 10 2017 SOR T4
19 NOVEMBER 10 2017 SOR T2
505,849 NOVEMBER 10 2017 SOR T5
149,524,212 NOVEMBER 10 2017 SOR T6
43,465,416 NOVEMBER 10 2017 SOR T7
689,495 NOVEMBER 10 2017 SOR T3
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Mon Nov 27, 2017 4:09 pm Post subject:
Suchay,
Here is an untested version that will give you the summation of t16 records. You can avoid the multiple IFTHEN statements on OUTFIL using symbols and CHANGE command, but I am not going to spend anymore time on fine tuning it. you should work on it and make it universal that you don't need to change the control cards for every year or region.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum