Summarize field amounts by groups



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

Summarize field amounts by groups

Postby claywilly » Mon Sep 29, 2008 11:18 pm

Hello..
I have a flat file that contains an 11 byte voucher number field and 4 amount fields, each are ZD of 14.

I need to summarize or total up the 1st amount field if the voucher numbers are the same.

Sample records... The xxx's are the voucher number, the 999's is the field i want to sum up. Spaces are inserted for readability.

...............................xxxxxxxxxxx....99999999999999
PN N1000006689 N1000006689 003 XLR00004676 01 00000002550000 00000018989000 00000006545000
PN F2000002408 F2000002408 003 XJD00006212 09 00000000054018 00000011420000 00000000000000
PN F2000002408 F2000002408 003 XJD00006212 10 00000000015365 00000011420000 00000000000000
PN F2000002408 F2000002408 003 XJD00006212 08 00000000078448 00000011420000 00000000000000
PN F2000002408 F2000002408 003 XJD00006212 07 00000000041424 00000011420000 00000000000000
PN N1000005111 N1000005111 003 PJD00037451 01 00000000565704 00000007071301 00000007071300


Result..
...............................xxxxxxxxxxx....99999999999999
PN N1000006689 N1000006689 003 XLR00004676 01 00000002550000 00000018989000 00000006545000
PN F2000002408 F2000002408 003 XJD00006212 09 00000000189255 00000011420000 00000000000000
PN N1000005111 N1000005111 003 PJD00037451 01 00000000565704 00000007071301 00000007071300


Thanks,
clay
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Summarize field amounts by groups

 

Re: Summarize field amounts by groups

Postby Frank Yaeger » Tue Sep 30, 2008 1:01 am

You can use a DFSORT job like this. I assumed that your output records could be sorted by the voucher number. If they really need to be sorted in their original order, let me know. I also assumed that the voucher number field starts position 32 and the amount field starts in positin 47.

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB)
//SORTOUT DD DSN=...  output file (FB)
//SYSIN    DD    *
    SORT FIELDS=(32,11,CH,A)
    OPTION ZDPRINT
    SUM FIELDS=(47,14,ZD)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Summarize field amounts by groups

Postby claywilly » Tue Sep 30, 2008 1:51 am

Thanks Frank, That worked.

So how can I integrate your code with the rest of my sort? Should I use two steps, one with iceman, then the other with sort?

...
//S2 EXEC PGM=SORT,
// REGION=4096K
//*
//SORTIN DD DSN=...input
//*
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTWK01 DD UNIT=SYSDA,...
//SORTWK02 DD UNIT=SYSDA,...
//*
//SORTOUT DD DSN=...output
//*
//SYSIN DD *
SORT FIELDS=COPY

OUTREC FIELDS=(1,02, . REF-TRANS-CODE(O)
3,11, . PA-NUMBER(P)
14,11, . REF-PO-NUMBER(O)
25,03, . VOUCHER-AGCY(O)
28,11, . VOUCHER-NUMBER(O)
39,02, . LINE-NO(O)
41,14, . VOUCHER-AMOUNT(O)
55,14, . AUTHORIZED-AMOUNT(P)
69,14, . ENCUMBERED-AMOUNT(P)
83,14, . EXPENDED-AMOUNT(P)
97,14, . CLOSED-AMOUNT(P)
111,14, . CLOSED-AMOUNT(O)
129,02,C'/', . START-DATE-MONTH(P)
131,02,C'/', . START-DATE-DAY(P)
125,04, . START-DATE-YEAR(P)
137,02,C'/', . END-DATE-MONTH(P)
139,02,C'/', . END-DATE-DAY(P)
133,04, . END-DATE-YEAR(P)
(55,14,ZD,SUB,97,14,ZD,SUB,111,14,ZD),
EDIT=(SIIIIIIIIIIIT.TT),
SIGNS=(+,-))
/*

*
User avatar
claywilly
 
Posts: 26
Joined: Sat Jun 14, 2008 12:01 am
Has thanked: 0 time
Been thanked: 0 time

Re: Summarize field amounts by groups

Postby Frank Yaeger » Tue Sep 30, 2008 2:33 am

So how can I integrate your code with the rest of my sort? Should I use two steps, one with iceman, then the other with sort?


That statement makes no sense. PGM=ICEMAN and PGM=SORT both call DFSORT.

It appears your job just copies the records and reformats them with an OUTREC statement. If you're ok with having the output in sorted order by the voucher number field, then you can just add your OUTREC statement to my job (OUTREC statement processing is performed after SORT and SUM processing). I've adjusted my positions per what you showed in your job.

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB)
//SORTOUT DD DSN=...  output file (FB)
//SYSIN    DD    *
  SORT FIELDS=(28,11,CH,A)
  OPTION ZDPRINT
  SUM FIELDS=(41,14,ZD)
  OUTREC FIELDS=(1,02, . REF-TRANS-CODE(O)
     3,11, . PA-NUMBER(P)
    14,11, . REF-PO-NUMBER(O)
    25,03, . VOUCHER-AGCY(O)
    28,11, . VOUCHER-NUMBER(O)
    39,02, . LINE-NO(O)
    41,14, . VOUCHER-AMOUNT(O)
    55,14, . AUTHORIZED-AMOUNT(P)
    69,14, . ENCUMBERED-AMOUNT(P)
    83,14, . EXPENDED-AMOUNT(P)
    97,14, . CLOSED-AMOUNT(P)
   111,14, . CLOSED-AMOUNT(O)
   129,02,C'/', . START-DATE-MONTH(P)
   131,02,C'/', . START-DATE-DAY(P)
   125,04, . START-DATE-YEAR(P)
   137,02,C'/', . END-DATE-MONTH(P)
   139,02,C'/', . END-DATE-DAY(P)
   133,04, . END-DATE-YEAR(P)
   (55,14,ZD,SUB,97,14,ZD,SUB,111,14,ZD),
    EDIT=(SIIIIIIIIIIIT.TT),
    SIGNS=(+,-))
/*


If you want the output records in their original order (like a copy), let me know.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post