Tallying debits and credits in two files



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

Tallying debits and credits in two files

Postby Aadil » Fri Jan 30, 2009 11:48 pm

Can someone please assist me with the following:

I have two files, each containing records containing debit and credit transactions. Each files debits and credits must be tallied up separately and the net amount displayed. So for example, for FILE A, Total Debits = xxxxx, Total Credits = yyyyy, Net amount = xxxx-yyyyy
FILE B, Total Debits = aaaaa, Total Credits = bbbbb, Net amount = aaaaa-bbbbb

File A is as follows:

1...................52 <=== column

RECFM=FB, LRECL=180, BLKSIZE=27900 <=== File attributes


0460820901260901260901260901260000010001SAMEDAY
6019490501949001172608200000125500562005846459300000
6019490501949001172608200000225210550300063092300000
6019490501949001172608200000302000900070013519300000
6019490501949001172608200000405553400043121381300000
6019490501949001172608200000523013755071181959300000
6019490501949001172608200000644011003492824302300000
6019490501949001172608200000732334504054478516300000
6019490501949001172608200000825500562005846459300000

Column 52: defined as PIC 9(09)V99
Amount

Column 1: defined as PIC 99
20 ==> Debit
60 ==> Credit


File B is as follows:

81.........................108........................135 <=== column

RECFM=FB, LRECL=310, BLKSIZE=27900 <=== File attributes


01000000146905930400000469114372009012600000000000000+0000000370136
01000000151102978100000511114372009012600000000000000+0000000370136
01000000194900007900000949114372009012600000000000000+0000000284870
01000000197060799800000970114372009012600000000000000+0000000370136
01000000197060799800000970114372009012600000000000000+0000000370136
01000000145403413000000454114372009012600000000000000+0000000370136
01000000190410948900000904114372009012600000000000000+0000000370136
01000000118601906900000186114372009012600000000000000+0000001511811
01000000149710233200000497114372009012600000000000000+0000000370136
01000000103633819300000036114372009012600000000000000+0000000370136
01000000190741548300000907114372009012600000000000000+0000000370136
01000000168607544800000686114372009012600000000000000+0000000370136

Column 108: defined as PIC 9
1 ==> Debit
6 ==> Credit

Column 135: defined as PIC 9(11)V99
Amount

Thanks.

Aadil
Aadil
 
Posts: 6
Joined: Fri Aug 29, 2008 4:02 am
Has thanked: 1 time
Been thanked: 0 time

Re: Tallying debits and credits in two files

 

Re: Tallying debits and credits in two files

Postby Frank Yaeger » Sat Jan 31, 2009 2:54 am

I can't really tell where the fields you mention are, and I'm not sure exactly what you want for output (data records and totals or just totals?), but assuming you just want the totals, you can use a DFSORT job like the following for your FileA example. You can use something similar for your FileB example.
//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/180)
//SORTOUT DD DSN=...  output file (FB/40)
//SYSIN    DD    *
  OPTION COPY
  INREC IFTHEN=(WHEN=(1,2,CH,EQ,C'20'),
     BUILD=(1:52,11,24:C'+',52,11)),
        IFTHEN=(WHEN=(1,2,CH,EQ,C'60'),
     BUILD=(12:52,11,24:C'-',52,11))
  OUTFIL REMOVECC,NODETAIL,
    BUILD=(40X),
    TRAILER1=('Total Debits  = ',TOT=(1,11,ZD,M25),/,
              'Total Credits = ',TOT=(12,11,ZD,M25),/,
              'Net Amount    = ',TOT=(24,12,SFF,M25))
/*


If that doesn't do what you want, then you have to do a better job of explaining exactly what it is you do want. An example of your input records with just the relevant fields and the expected output records would help.
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: Tallying debits and credits in two files

Postby Aadil » Mon Feb 02, 2009 11:59 am

Thanks for the help Frank. The totals is what I was looking for.
Aadil
 
Posts: 6
Joined: Fri Aug 29, 2008 4:02 am
Has thanked: 1 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post