AGGREGATE AND AVERAGE PRICE



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

AGGREGATE AND AVERAGE PRICE

Postby zost » Wed Oct 03, 2007 4:56 pm

Hello,
I was wondering whether the following could be accomplished by using DFSORT instead of writing a program.
Thanks in advance.

Input information (ordered by product)

Product Quantity Price Gross
----------------------------------------------------
PRO1 180 7.62 1371.60
PRO1 12 7.62 91.44
PRO1 110 7.61 837.10
PRO1 220 7.65 1683.00

Format of expected input information:

PRO1001800007620137160
PRO1000120007620009144
PRO1001100007610083710
PRO1002200007650168300


Output information:
Product Quantity Avg.Price Gross
---------------------------------------------------------
PRO1 522 7.63 3983.14

Desired format of output information:

PRO1005220007630398314


That is, for each given product (in the example only PRO1), obtain the total quantity, and also the total gross (with its corresponding price), and the average price.
zost
 
Posts: 6
Joined: Wed Oct 03, 2007 4:33 pm
Has thanked: 1 time
Been thanked: 0 time

Re: AGGREGATE AND AVERAGE PRICE

Postby CICS Guy » Wed Oct 03, 2007 5:29 pm

There is an excellent example of trailer1 processing averages and totals in 3.13.2.4 Example 4 in the dfsort manual......
CICS Guy
 
Posts: 246
Joined: Wed Jun 20, 2007 4:08 am
Has thanked: 0 time
Been thanked: 0 time

Re: AGGREGATE AND AVERAGE PRICE

Postby zost » Wed Oct 03, 2007 6:08 pm

I'll have a look.
Thanks a lot.
zost
 
Posts: 6
Joined: Wed Oct 03, 2007 4:33 pm
Has thanked: 1 time
Been thanked: 0 time

Re: AGGREGATE AND AVERAGE PRICE

Postby zost » Wed Oct 03, 2007 6:31 pm

CICS Guy wrote:There is an excellent example of trailer1 processing averages and totals in 3.13.2.4 Example 4 in the dfsort manual......


Hi CICS-Guy,

Thanks for your quick reply, but I think you misunderstood me. I'm not interested in the formatting/editing features of DFSORT. Instead, the calculations themselves. But as you can see, the output records include the calculations (the total sum of quantity, average price per product, and the gross, taking into accout the specific rpice of each input line).

I think this is difficult to explain, but I wouldn't like to write a program to just calculate totals per product.
zost
 
Posts: 6
Joined: Wed Oct 03, 2007 4:33 pm
Has thanked: 1 time
Been thanked: 0 time

Re: AGGREGATE AND AVERAGE PRICE

Postby Frank Yaeger » Wed Oct 03, 2007 9:11 pm

zost,

You can use a DFSORT job like this to do what you asked for:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
PRO1001800007620137160
PRO1000120007620009144
PRO1001100007610083710
PRO1002200007650168300
PRO2001800007620137160
PRO2000120007620009144
PRO2001100007610083710
PRO2002200007650168300
/*
//SORTOUT DD SYSOUT=*
//SYSIN    DD    *
  OPTION COPY
  OUTFIL REMOVECC,NODETAIL,
    SECTIONS=(1,4,
     TRAILER3=(1,4,
      TOT=(5,5,ZD,TO=ZD,LENGTH=5),
      AVG=(10,6,ZD,TO=ZD,LENGTH=6),
      TOT=(16,7,ZD,TO=ZD,LENGTH=7)))
/*


SORTOUT would have:

PRO1005220007620398314
PRO2005220007620398314


Note that the average here is 762, not 763. The AVG function gives TOT/COUNT rounded down to the nearest integer, so 3050/4 = 762.5 is rounded down to 762.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/servers/storage/supp ... tmpub.html
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