Page 1 of 1

Selecting Maximum Value

PostPosted: Thu May 22, 2008 8:09 pm
by makies
Hello I have to sort the following data in order to geth the shop which has the biggest sales for each month for example. The first field is shop location, the second is the month and the third is total sales. Column leghts are 1,17 for locations, 19,2 for monts, 22,6 for sales
london                  1         300
london                  2         150
milan                    1         310
milan                   3         120
Sacramento          2         100
Munich


I woul like display the shop with the biggest sales for each month

Expected output
1    milan
2    london
3    milan


Thank you very much

Re: Selecting Maximum Value

PostPosted: Thu May 22, 2008 8:51 pm
by Frank Yaeger
Here's a DFSORT/ICETOOL job that will do what you asked for:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB)
//OUT DD DSN=...  output file (FB)
//TOOLIN  DD    *
SELECT FROM(IN) TO(OUT) ON(19,2,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  SORT FIELDS=(19,2,CH,A,22,6,ZD,D)
  OUTFIL FNAMES=OUT,BUILD=(19,2,6:1,15)
/*

Re: Selecting Maximum Value

PostPosted: Thu May 22, 2008 8:55 pm
by makies
Thanks for your time but can you explain how the code you 've written will select the maximum sales for each month??

Re: Selecting Maximum Value

PostPosted: Thu May 22, 2008 9:02 pm
by Frank Yaeger
SORT FIELDS=(19,2,CH,A,22,6,ZD,D) sorts by date ascending and by sales descending, so the intermediate result for that would be:

milan              1    310   
london             1    300   
london             2    150   
Sacramento         2    100   
milan              3    120   


SELECT ... ON(19,2,CH) FIRST selects the first record for each month, so that intermediate result would be:

milan              1    310   
london             2    150   
milan              3    120   


Finally, the OUTFIL statement reformats the record with just the month and shop, so the final result would be:

 1   milan     
 2   london     
 3   milan     

Re: Selecting Maximum Value

PostPosted: Thu May 22, 2008 9:05 pm
by makies
Thank you very much that was really helpful, I really appreciate this man

Re: Selecting Maximum Value

PostPosted: Fri May 23, 2008 2:40 am
by makies
//MYJOB    JOB USER=SUR0016,MSGCLASS=H,CLASS=A,NOTIFY=SUR0016
//S1       EXEC PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                       
//DFSMSG   DD SYSOUT=*                                       
//IN       DD DSN=SURMSTR.TRDATA2,DISP=SHR                   
//OUT      DD SYSOUT=*                                       
//TOOLIN   DD *                                               
  SELECT FROM(IN) TO(OUT) ON(24,2,ZD) FIRST USING(CTL1)       
/*                                                           
//CTL1CNTL DD *                                               
  SORT FIELDS=(24,2,ZD,A,27,7,ZD,D)                           
  OUTFIL FNAMES=OUT,BUILD=(24,2,7:1,16)                       
/*                                                           


i am getting an error MAXCC=16, does anyone know what's wrong with this one

Re: Selecting Maximum Value

PostPosted: Fri May 23, 2008 2:53 am
by Frank Yaeger
i am getting an error MAXCC=16, does anyone know what's wrong with this one


There's nothing obviously wrong with it and when I ran it here, I got RC=0. So something is different in what you're doing or in your shop.

The RC=16 indicates that DFSORT detected an error. It displays an error message to tell you what that error is. Look in the DFSMSG data set and you will see an error message of the form ICExxxA. You can look up that error message here:

http://publibz.boulder.ibm.com/cgi-bin/ ... 0721170811

If you can't figure out what it means from that, then post the DFSMSGS messages you received.

Re: Selecting Maximum Value

PostPosted: Fri May 23, 2008 2:59 am
by makies
where can you see the DFsMSG error ?? The error that i am getting after i submit the job is

21.22.37 JOB05827 $HASP165 MYJOB ENDED AT TSTMVS01 MAXCC=16 CN(INTERNAL)
***

Re: Selecting Maximum Value

PostPosted: Fri May 23, 2008 3:44 am
by Frank Yaeger
This is the same problem you're having in your other thread with not being able to see the SYSOUT messages.

You can write //TOOLMSG and //DFSMSG to permanent data sets so you can see the messages.

Change the DD statements to something like:

//TOOLMSG DD DSN=SURMSTR.TOOLSMG,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(5,5)),UNIT=SYSDA
//DFSMSG DD DSN=SURMSTR.DFSMSG,DISP=(NEW,CATLG,DELETE),
// SPACE=(TRK,(5,5)),UNIT=SYSDA

Then you should be able to see the messages in SURMSTR.TOOLMSG and SURMST.DFSMSG.

The next time you run, be sure to change //TOOLMSG and //DFSMSG to

//TOOLMSG DD DSN=SURMSTR.TOOLMSG,DISP=OLD
//DFSMSG DD DSN=SURMSTR.DFSMSG,DISP=OLD

since they are already allocated.