MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Create Total-Amount from two different keys

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
hungerbuehler
Beginner


Joined: 25 Mar 2007
Posts: 11
Topics: 4
Location: Switzerland
PostPosted: Tue Dec 08, 2009 12:53 pm    Post subject: Create Total-Amount from two different keys Reply with quote
Hi all,

I'm trying to create a file with a new colums totals. One of the sum-calculation is negative:

This is an example from the input-file:
All numbers are in PD-format, length=16
Start-pos column 1: position 1
Start-pos column 2: position 8
Start-pos column 3: position 20 (PD, length=16)
Code:

Debit1  Credit10     700
Debit8  Credit50      50
Debit4  Credit10      40
Debit3  Credit40     100
Debit3  Credit50      70
Debit1  Credit10     300
Debit8  Credit40     100


Output schould be ( The order is not necessary):
Code:

Debit1     000000000    1000
Debit8     000000000     150
Debit4     000000000      40
Debit3     000000000     170
Credit10   000000000    1040-
Credit50   000000000     120-
Credit40   000000000     200-


Is this possible with the ICETOOL? Within one step (one time to read the input-file)?

Here is my (beginner)-solution. But i need two steps and i have problems with the negative sign.
Code:

//S02 EXEC PGM=ICETOOL                                     
//TOOLMSG DD SYSOUT=*                                     
//DFSMSG  DD SYSOUT=*                                     
//IN1     DD DSN=WOK.INPUT,DISP=SHR   
//T1      DD DSN=WOK.OUTPUT.T1,                           
//           DISP=(MOD,CATLG,DELETE),                     
//           RECFM=FB,LRECL=191                           
//TOOLIN   DD    *                               
  COPY FROM(IN1) TO(T1) USING(CTL1)             
  COPY FROM(IN1) TO(T1) USING(CTL2)             
/*                                       
//CTL1CNTL DD *                           
  INREC OVERLAY=(140:+0,TO=PD,LENGTH=16) 
  SORT FIELDS=(1,6,CH,A)                 
  OUTFIL REMOVECC,NODETAIL,               
    SECTIONS=(1,6,                       
      TRAILER3=(1,6,             
                C'   ',                     
                140,16                     
                TOT=(20,16,PD,TO=PD)))   
/*                                       
//CTL2CNTL DD *                                             
  INREC OVERLAY=(140:+0,TO=PD,LENGTH=16,160:+0,SUB,48,16,PD)
  SORT FIELDS=(9,8,CH,A)                                   
  OUTFIL REMOVECC,NODETAIL,                                 
    SECTIONS=(9,8,                                         
      TRAILER3=(9,8,
                C' ',                                         
                TOT=(160,16,PD)))     <== here i have a fault
/*                                                           

I cannot create a negative sum. (TOT=(160,16,PD))

thank you very much in advance for the support or for a easier solution.

hungerb
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Tue Dec 08, 2009 7:46 pm    Post subject: Reply with quote
What is the RECFM and LRECL of your input file?

Are you trying to get separate totals for each "key" in each of the two columns? Why do you get negative values from the positive values for the second column (e.g. Credit50 = 50 + 70 = -120 instead of +120?).

You need to do a better job of explaining the rules for getting from input to expected output.

Quote:
I cannot create a negative sum. (TOT=(160,16,PD))


You have to tell DFSORT how to format the total:

TOT=(160,16,PD,EDIT=(...),SIGNS=(...))

or

TOT=(160,16,PD,TO=FS)

depending on what you want the output to look like exactly.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
hungerbuehler
Beginner


Joined: 25 Mar 2007
Posts: 11
Topics: 4
Location: Switzerland
PostPosted: Wed Dec 09, 2009 12:49 am    Post subject: Reply with quote
many thanks for the answer,

The input file has following format:
RECFM=FB
LRECL=200

Quote:
Are you trying to get separate totals for each "key" in each of the two columns? Why do you get negative values from the positive values for the second column (e.g. Credit50 = 50 + 70 = -120 instead of +120?).

Yes, absolute correct.
Reason:
One record means one booking (from the credit-account to the debit-account). We take the money from the credit-account (-/negativ) to the debit-account (+/positive).

Quote:
You need to do a better job of explaining the rules for getting from input to expected output.

The CTL1 (for the positve values, Debitx) works fine. I create a new column with the total from the input-values (the results are in the format PD)
The CTL2 does not work. I need to create the total from all values with Creditxx, but i need the negative value.

My idea for the negative value:
i create with "INREC OVERLAY" the negative sum at position 160:
The previous example had an error (position).
Now correct:
Code:
INREC OVERLAY=(140:+0,TO=PD,LENGTH=16,160:+0,SUB,20,16,PD)


And in the TRAILER3 i try to create the total (negative) from the pos 160:
Code:
TOT=(160,16,PD,TO=PD)


But the DFSORT abended with a S0C7-Abend.


Quote:
You have to tell DFSORT how to format the total

I need the format PD,length=16 for all numbers in this example.

If you need more information ore more examples, pleas inform me.

Thanks
hungerbuehler
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Thu Dec 10, 2009 2:09 pm    Post subject: Reply with quote
Based on what you've said, you could use a DFSORT/ICETOOL job like the following to do what you asked for:

Code:

//S02 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN1     DD DSN=WOK.INPUT,DISP=SHR
//T1      DD DSN=WOK.OUTPUT.T1,
//           DISP=(MOD,CATLG,DELETE),
//           RECFM=FB,LRECL=191
//TOOLIN   DD    *
COPY FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,6,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    BUILD=(1,200),
    SECTIONS=(1,6,
      TRAILER3=(1,6,
                11:15X'00',X'0C',
                27:TOT=(20,16,PD,TO=PD,LENGTH=16)))
/*
//CTL2CNTL DD *
  INREC OVERLAY=(140:20,16,PD,MUL,-1,TO=PD,LENGTH=16)
  SORT FIELDS=(9,8,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    BUILD=(1,191),
    SECTIONS=(9,8,
      TRAILER3=(9,8,
                11:15X'00',X'0C',
                27:TOT=(140,16,PD,TO=PD,LENGTH=16)))
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
hungerbuehler
Beginner


Joined: 25 Mar 2007
Posts: 11
Topics: 4
Location: Switzerland
PostPosted: Mon Dec 14, 2009 11:45 am    Post subject: Reply with quote
Hi Frank,

Thank you very much for your support.
With your example....it works!!!

Best Regards
Hungerbuehler
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group