ICETOOL



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

Re: ICETOOL

Postby eric12326 » Wed Apr 24, 2013 2:10 am

Thanks this is my level of Z/OS DFSORT V1R5
 001258 bradford          1999  4     139     779
 001259 bradford          1999  5     175     799
 001260 bradford          1999  6     182     758
 001261 bradford          1999  7     225     233
 001262 bradford          1999  8     205     587
 001263 bradford          1999  9     207    1131
 001264 bradford          1999 10     146     933
 001265 bradford          1999 11     112     335
 001266 bradford          1999 12      81    1804
 001267 bradford          2000  1      86     716
 001268 bradford          2000  2      99     720
 001269 bradford          2000  3     115     532
 001270 bradford          2000  4     123    1207
 001271 bradford          2000  5     170     503
 001272 bradford          2000  6     190     760


and sample data
eric12326
 
Posts: 9
Joined: Thu Apr 04, 2013 8:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Re: ICETOOL

 

Re: ICETOOL

Postby Akatsukami » Wed Apr 24, 2013 2:17 am

eric12326 wrote:Thanks this is my level of Z/OS DFSORT V1R5

As indicated in this post by Frank Yaeger, Mr Kolusu will need to see the ICE201I message (and preferably the rest of the messages from DFSORT) to determine the exact version, including PTFs applied. Please conquer your fears and paste the Coded messages into this thread.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1054
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: ICETOOL

Postby dick scherrer » Wed Apr 24, 2013 2:34 am

Hello,

From your sample data, what do you want as output when your process is run?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: ICETOOL

Postby eric12326 » Wed Apr 24, 2013 4:06 pm

Hello D.S,

From the sample data I,m looking to generate a report for each month(24,2) indicating the best performing location(1,17) for sales(27,7) summed over a 20 yr period.

So far I can generate a report a report listing all sales/months for all years.

But what I want is a report showing the best location sales per month summed over the 20 yr period ie: 1-12

Instead I get Duplicates for all years.

Sorry if I'm not explaining myself very well, I am starting to tie myself in knots !!!!!

As mentioned I have tried SELECT, FIRST, FIRSTDUP and OCCUR to try and get the top grossing location 1-12 over all yearly sales to no avail as yet.

Currently trawling through DFSORT application programming guide for help.

Thanks Newbie
eric12326
 
Posts: 9
Joined: Thu Apr 04, 2013 8:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: ICETOOL

Postby skolusu » Wed Apr 24, 2013 10:25 pm

eric12326 wrote:Hello D.S,

From the sample data I,m looking to generate a report for each month(24,2) indicating the best performing location(1,17) for sales(27,7) summed over a 20 yr period. So far I can generate a report a report listing all sales/months for all years. But what I want is a report showing the best location sales per month summed over the 20 yr period ie: 1-12
Sorry if I'm not explaining myself very well, I am starting to tie myself in knots !!!!!


Eric,

You are actually confusing us , well at least me.

When you say you want the best location per month, do you ignore the year? if you do then you would get 1 record for each location. let take this sample data for 2 locations for month april but spread across 3 years.

001258 BRADFORD   1999  4     139
001258 BRADFORD   2000  4     140
001258 BRADFORD   2001  4     150

001270 BRADFORD   1999  4     300
001270 BRADFORD   2000  4     400
001270 BRADFORD   2001  4     500


When you ignore the year and sum on the location+month you end up with 2 rows like this
001258 BRADFORD  1999 4       429
001270 BRADFORD  1999 4      1200


Now do you want to pick the highest sales amount value? ie 1200?
001270 BRADFORD  1999 4      1200


You need to provide examples like I shown so that it is easy for us to understand as to what you are trying to do. Btw do you have embedded spaces for the sales amount?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: ICETOOL

Postby eric12326 » Thu Apr 25, 2013 1:34 am

Sorry if I confusing matters,

My source data consists of 20 years of sales figures. This is the actual question.

"For each month, report the best performing location for car sales."

so basically what I want is the best selling location over the 20 yrs for jan,feb,march and so on.
The output should show 12 best performing locations for the 12 months summed over the 20 years.

this is a sample of what I have. I need to omit the duplicates and keep only the highest sales per month.

bradford                             6                 7083
braemar                              8                 7045
braemar                              7                 7043
durham                               6                 7039
bradford                             9                 6725
cardiff                              8                 6715
bradford                             5                 6091
newtonrigg                           5                 6066
braemar                              9                 5853
cambridge                           10                 5853
lerwick                              8                 5834
stornoway                            9                 5782
rossonwye                           10                 5689
leuchars                             5                 5643
braemar                              5                 5296
cambridge                            4                 5282 
eric12326
 
Posts: 9
Joined: Thu Apr 04, 2013 8:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: ICETOOL

Postby skolusu » Thu Apr 25, 2013 2:11 am

Eric,

You really need to work on show what you have and what you finally need. You show the input without duplicates data for the months and you never ever show the final output you need. Any way here is a stab at it from what I gathered so far.
The layout I assumed is
Location     01-17
Month        24-25
Sales amount 27-33

//STEP0100 EXEC PGM=SORT               
//SYSOUT   DD SYSOUT=*                 
//INA      DD DISP=SHR,DSN=Your input file
//INB      DD DISP=SHR,DSN=Same input file again
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  JOINKEYS F1=INA,FIELDS=(1,19,A)                           
  JOINKEYS F2=INB,FIELDS=(1,19,A)                           
  REFORMAT FIELDS=(F1:1,19,F2:20,5)                         

  SORT FIELDS=(18,2,CH,A,20,5,ZD,D),EQUALS                 

  OUTREC OVERLAY=(20:20,5,PD,M10,LENGTH=9)                 

  OUTFIL REMOVECC,NODETAIL,BUILD=(80X),                     
  HEADER1=(DATE,8X,'BEST PERFORMING LOCATION REPORT',       
           12X,'PAGE : ',PAGE,/,/,                         
           'LOCATION',25X,'MONTH',16X,'SALES',/,           
           17C'-',5X,16C'-',5X,16C'-'),                     
  SECTIONS=(18,2,HEADER3=(1,17,19X,18,2,12X,20,9)),         
  TRAILER1=(44:16C'-',/,4:'TOTAL SALES : ',                 
            50:TOT=(20,9,UFF,M10,LENGTH=10))               
//*                                                         
//JNF1CNTL DD *                                             
  INREC BUILD=(01,17,                  $ LOCATION           
               24,02)                  $ MONTH             
  SUM FIELDS=NONE                      $ REMOVE DUPS       
//*                                                         
//JNF2CNTL DD *                                             
  INREC BUILD=(01,17,                 $ LOCATION           
               24,02,                 $ MONTH               
               27,7,UFF,PD,LENGTH=5)  $ SALES AMOUNT       
  SUM FIELDS=(20,5,PD)                $ SUM SALES AMOUNT   
//*


The output will be something like this
04/24/13        BEST PERFORMING LOCATION REPORT            PAGE :      1
                                                                       
LOCATION                         MONTH                SALES             
-----------------     ----------------     ----------------             
CAMBRIDGE                            4                 5282             
NEWTONRIGG                           5                 6066             
DURHAM                               6                 7039             
BRAEMAR                              7                 7043             
LERWICK                              8                 5834             
STORNOWAY                            9                 5782             
CAMBRIDGE                           10                 5853             
                                           ----------------             
   TOTAL SALES :                                      99039             


If that is not what you want you need to show the Sample INPUT and DESIRED OUTPUT
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

These users thanked the author skolusu for the post:
eric12326 (Thu Apr 25, 2013 2:58 am)
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: ICETOOL

Postby eric12326 » Thu Apr 25, 2013 2:57 am

thanks this looks close to what I need and should give me something to work with.

I appreciate all the help and your patients .

Newbie
eric12326
 
Posts: 9
Joined: Thu Apr 04, 2013 8:48 pm
Has thanked: 1 time
Been thanked: 0 time

Re: ICETOOL

Postby skolusu » Thu Apr 25, 2013 3:47 am

I had a typo in the control cards

The sort fields statement should be sorting the sales amount in PD format rather than ZD format. Sorry about that.
SORT FIELDS=(18,2,CH,A,20,5,PD,D),EQUALS
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Previous

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post