Selecting Maximum Value



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

Selecting Maximum Value

Postby makies » Thu May 22, 2008 8:09 pm

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
makies
 
Posts: 14
Joined: Wed May 21, 2008 5:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Maximum Value

Postby Frank Yaeger » Thu May 22, 2008 8:51 pm

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)
/*
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: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Selecting Maximum Value

Postby makies » Thu May 22, 2008 8:55 pm

Thanks for your time but can you explain how the code you 've written will select the maximum sales for each month??
makies
 
Posts: 14
Joined: Wed May 21, 2008 5:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Maximum Value

Postby Frank Yaeger » Thu May 22, 2008 9:02 pm

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     
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: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Selecting Maximum Value

Postby makies » Thu May 22, 2008 9:05 pm

Thank you very much that was really helpful, I really appreciate this man
makies
 
Posts: 14
Joined: Wed May 21, 2008 5:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Maximum Value

Postby makies » Fri May 23, 2008 2:40 am

//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
makies
 
Posts: 14
Joined: Wed May 21, 2008 5:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Maximum Value

Postby Frank Yaeger » Fri May 23, 2008 2:53 am

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.
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: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: Selecting Maximum Value

Postby makies » Fri May 23, 2008 2:59 am

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)
***
makies
 
Posts: 14
Joined: Wed May 21, 2008 5:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Selecting Maximum Value

Postby Frank Yaeger » Fri May 23, 2008 3:44 am

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.
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: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post