DFSORT Sum and count



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

DFSORT Sum and count

Postby aerosmith » Thu Feb 27, 2014 8:41 pm

Good Morning,

Im new to DFSORT and im trying to sort/count and sum if possible something similar to Sum and count something similar to



ID                 Reversal        Original   RCode     Ocode
20-009407            0.00           18.00       0420           
20-009407           18.00           0.00               0210
20-009407           18.00           0.00               0210





My desired outcome would be one line for unique ID, Sum the dollar amounts and Count the Codes
20-009407           36.00        18.00       1         2
aerosmith
 
Posts: 2
Joined: Thu Feb 27, 2014 8:34 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DFSORT Sum and count

 

Re: DFSORT Sum and count

Postby BillyBoyo » Thu Feb 27, 2014 9:38 pm

Have a look at the OUTFIL reporting functions. SECTIONS and TRAILER3, specifically, if you want intermediate totals, or TRAILER2 if you just want a final total. Assuming that you want the count of the existence of values for your RCode and Ocode (why the different format?) then you'd extend your data by two fields, which contain a value of "0" for a blank value and otherwise "1" and then use TOT for those.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: DFSORT Sum and count

Postby aerosmith » Fri Feb 28, 2014 8:25 am

Thanks, i took your advise and the way the input file was causing me to confuse myself, so i decided to seperate them in two different files.

Im getting an error now

            SORT FIELDS=(COPY)                                       
            OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH;EQ;C'0-');AND;     
                                           (143,4,CH;EQ;C'0420'))     
                   SECTIONS=(43,9                                     
                   $                                                 
ICE005A 0 BLANK NEEDED IN COLUMN 1 OR OPERATION NOT DEFINED CORRECTLY
                   TRAILER3=(43,9,X,COUNT=(M11,LENGTH=9))




sorry im new to this , and finding it very interesting :)


i added the trailer3 part and getting the the error before that the seperation ran ok.

 //TOOLIN   DD *                                         
   COPY FROM(INDD1) USING(CTL1)                         
 //CTL1CNTL DD *                                         
   SORT FIELDS=(COPY)                                   
   OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH;EQ;C'0-');AND;
                                  (143,4,CH;EQ;C'0420'))
          SECTIONS=(43,9                                 
          TRAILER3=(43,9,X,COUNT=(M11,LENGTH=9))         
   OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH;EQ;C'0-');AND;
                                  (153,4,CH;EQ;C'0321'))



Sample of input
20-016600  TS2001660001      001  882  016  999999  18.00           0.00     0420
20-018713  TS2001871301      001  364  063  999999  14.00           0.00     0420
20-018713  TS2001871301      001  364  063  999999  29.00           0.00     0420
Position 43                                                                                               Position 143




Ideal output
20-016600    1
20-018713    2


Code'd
aerosmith
 
Posts: 2
Joined: Thu Feb 27, 2014 8:34 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DFSORT Sum and count

Postby BillyBoyo » Fri Feb 28, 2014 4:03 pm

Your initial problems are missing commas. Change all your semi-colons to commas, and ensure that each line you want to continue ends with a comma before the first space, ie:

   OUTFIL FNAMES=OUTDD1,INCLUDE=((44,2,CH,EQ,C'0-'),AND, <- after than space, everything just comments


You can get it working a bit at a time and then put things together. It is a good way to understand what is going on.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post