a help with subtotal



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

a help with subtotal

Postby marijavi » Tue Sep 28, 2010 7:31 pm

I have the next file

KEY DATE AMMOUNT
0000001 20010101 001000
0000001 20020101 001500
0000001 20030101 000700
0000001 20040101 000550
0000002 20010101 002000
0000002 20020101 003500
0000002 20030101 000750
0000002 20040101 001550

I need to generate a file like this
KEY DATE AMMOUNT
0000001 20010101 001000 (this is the first record for the KEY 0000001)
0000001 20020101 002500 (this ammount is the first and second ammount added)
0000001 20030101 003200 (this ammount is the first, second and third ammount added)
0000001 20040101 003750 (this ammount is the first, second, third and the forth ammount added)
0000002 20010101 002000 (this is the first record for the KEY 0000002)
0000002 20020101 005500 (this ammount is the first and second ammount added)
0000002 20030101 006250 .. .. .. ...
0000002 20040101 007750 .. .. .. ....

I try the next:
//TOOLIN DD *
SORT FROM(IN) USING(CTL1)
//CTL1CNTL DD *
OPTION COPY
SORT FIELDS=(1,16,CH,A),EQUALS
OUTFIL FNAMES=OUT,REMOVECC,NODETAIL,
SECTIONS=(1,17,
TRAILER3=(1,17,SUBTOT=(18,6,ZD,EDIT=(TTTTTT))

and produce the next result:
0000001 20010101 001000 (ok, just I want)
0000001 20020101 002500 ((ok, just I want)
0000001 20030101 003200 (ok, just I want)
0000001 20040101 003750 (ok, just I want)
0000002 20010101 005750 (No ok, because I have added the previous amount, I need set the subtotal to zero previosly)
0000002 20020101 009250 " " "
0000002 20030101 010000 " " "
0000002 20040101 011550 " " "

Thanks a lot

(Regards from Madrid)
marijavi
 
Posts: 2
Joined: Mon Sep 27, 2010 4:47 pm
Has thanked: 0 time
Been thanked: 0 time

Re: a help with subtotal

 

Re: a help with subtotal

Postby dam_chandu » Wed Sep 29, 2010 2:52 pm

Try this JCL.. Its multi step JCL. Would be happy if someone can convert to a single step and make bit easier.

However worth a try..

//STEP0100 EXEC PGM=SORT                                               
//*                                                                    
//*----------------------------------------------------- I N P U T --- 
//SORTIN   DD *                                                        
0000001 20010101 001000                                                
0000001 20010101 001100                                                
0000001 20010101 001200                                                
0000001 20010101 001300                                                
0000001 20010101 001400                                                
0000002 20010101 002000                                                
0000002 20010101 002100                                                
0000002 20010101 002200                                                
0000002 20010101 002300                                                
//*                                                                    
//*--------------------------------------------------- O U T P U T --- 
//SYSOUT   DD  SYSOUT=A                                                
//SORTOUT  DD  DSN=&&TEST1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//SREC     DD  DSN=&&TEST2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//*                                                                    
//SYSIN    DD *                                                        
  SORT FIELDS=(1,7,CH,A)                                               
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(26:SEQNUM,8,ZD,RESTART=(1,7))),    
  IFTHEN=(WHEN=GROUP,BEGIN=(26,8,ZD,EQ,1),PUSH=(36:ID=8))              
  OUTFIL REMOVECC,NODETAIL,BUILD=(56X),                                
  SECTIONS=(1,7,26,8,                                                  
  TRAILER3=(1,24,X,36,8,36:SUBTOT=(18,6,SFF,EDIT=(TTTTTT))))           
  OUTFIL FNAMES=SREC,REMOVECC,NODETAIL,BUILD=(56X),                    
  HEADER1=(1,7,26:8C'0',45:10C'0'),                                    
  SECTIONS=(1,7,                                                       
  TRAILER3=(1,7,26:36,8,45:SUBTOT=(18,6,SFF,EDIT=(TTTTTT))))           
/*                                                                     
//STEP0200  EXEC PGM=ICETOOL                                           
//TOOLMSG   DD SYSOUT=*                                                
//DFSMSG    DD SYSOUT=*                                                
//IN        DD DSN=&&TEST1,DISP=SHR                                    
//          DD DSN=&&TEST2,DISP=SHR                                    
//T1        DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)             
//OUT1      DD SYSOUT=*                                                
//TOOLIN    DD *                                                       
  SPLICE FROM(IN) TO(T1) ON(26,8,CH) WITH(1,44) WITHALL USING(CTL1)    
  COPY FROM(T1) USING(CTL2)                                            
//CTL1CNTL DD *                                                        
  SORT FIELDS=(26,8,CH,A,45,10,CH,D)                                   
  INREC IFTHEN=(WHEN=(45,8,CH,GT,C' '),                                
  OVERLAY=(26:+1,ADD,26,8,ZD,M11,LENGTH=8))                            
  OUTFIL FNAMES=T1,                                                    
  BUILD=(1,23,X,45,6,UFF,SUB,36,6,ZD,M11,LENGTH=6)                     
//CTL2CNTL  DD *                                                       
  SORT FIELDS=(1,30,CH,A)                                              
  INREC OVERLAY=(18:25,6)                                              
  OUTFIL FNAMES=OUT1,SAVE,                                             
  BUILD=(1,23)                                                         
/*                                                                     

However point to note is this works only for positive values.

Cheers,
Anand.
dam_chandu
 
Posts: 6
Joined: Mon Sep 20, 2010 6:17 pm
Has thanked: 0 time
Been thanked: 0 time

Re: a help with subtotal

Postby marijavi » Wed Sep 29, 2010 2:58 pm

Hi

Many thanks by your answer

I'll try ....
regards.
marijavi
 
Posts: 2
Joined: Mon Sep 27, 2010 4:47 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER