Summing and splicing of records using ICETOOL



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

Summing and splicing of records using ICETOOL

Postby stat » Wed Dec 22, 2010 11:07 am

Hi All,

I have a typical requirement as shown below.But I am wondering if this can be achieved through icetool.

Input File
-----------
GRP
KEY Date CD Amount
--------------------------------------
AAAAA 2009-04-09 103 10
AAAAA 2009-04-09 104 20
AAAAA 2009-04-09 105 50
AAAAA 2009-02-06 104 40
AAAAA 2009-02-06 104 20
AAAAA 2009-02-06 105 90
AAAAA 2008-10-27 104 20
AAAAA 2008-10-27 105 70
AAAAA 2008-10-27 107 80


In the Input

1-5 : Key (5 bytes)
7-16: Date (10 bytes)
18-20: Grp CD (3 Bytes)
22-23: Amount (2 bytes)

In the output there should be one record for each date.

For a particular date, the summation of CD 103,104 should go to 1st Bucket in Output
For a particular date, the summation of CD 105,106 should go to 2nd Bucket in Output
For a particular date, the summation of CD 107,108 should go to 2nd Bucket in Output



Desired Output file
------------------------------------------------------------

Key Date 1st-Bucket 2nd-Bucket 3rdBucket
(1-5) (7-16) (18-19) (21-22) (23-24)

AAAAA 2009-04-09 30 50 0
AAAAA 2009-02-06 60 90 0
AAAAA 2008-10-27 20 70 80



I could proceed only upto the summation on CD Key/Date/CD combination.IFTHEN/SPLICE might
SORT Fileds=(1,5,CH,A,7,10,CH,D,18,3,CH,A)
SUM FIELDS=(22,2,CH)

Should I now split this file in six different subfiles based on the CD and then use them in splice?

But I believe you might have better ideas to acheive this.
stat
 
Posts: 3
Joined: Wed Dec 22, 2010 10:32 am
Has thanked: 0 time
Been thanked: 0 time

Re: Summing and splicing of records using ICETOOL

 

Re: Summing and splicing of records using ICETOOL

Postby Frank Yaeger » Wed Dec 22, 2010 10:58 pm

You don't need SPLICE for this. You can use a DFSORT job like the following to do what you asked for:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
AAAAA 2009-04-09 103 10
AAAAA 2009-04-09 104 20
AAAAA 2009-04-09 105 50
AAAAA 2009-02-06 104 40
AAAAA 2009-02-06 104 20
AAAAA 2009-02-06 105 90
AAAAA 2008-10-27 104 20
AAAAA 2008-10-27 105 70
AAAAA 2008-10-27 107 80
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  OPTION ZDPRINT
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(25:C'000000')),
   IFTHEN=(WHEN=(18,3,SS,EQ,C'103,104'),
    OVERLAY=(25:22,2)),
   IFTHEN=(WHEN=(18,3,SS,EQ,C'105,106'),
    OVERLAY=(27:22,2)),
   IFTHEN=(WHEN=(18,3,SS,EQ,C'107,108'),
    OVERLAY=(29:22,2))
   SORT FIELDS=(1,5,CH,A,7,10,CH,D)
   SUM FIELDS=(25,2,ZD,27,2,ZD,29,2,ZD)
   OUTREC BUILD=(1,16,18:25,2,21:27,2,24:29,2)
/*


SORTOUT would have:

AAAAA 2009-04-09 30 50 00 
AAAAA 2009-02-06 60 90 00 
AAAAA 2008-10-27 20 70 80 
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: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Summing and splicing of records using ICETOOL

Postby stat » Thu Dec 23, 2010 2:10 pm

Thanks a lot Frank. You are a genius.I have been following your post(through mvshelp.com which is paid forum now) for about 8 years and you have been always the one stop solution for any critical problems.

Just one more clarification please.I know it is a stupid question though because ideally I should not do that anyway.
Say in the final OUTREC if we printed the GRP CD(position 18-20) also in the single combined record,then will it necessarily follow any specific pattern?

I mean in your IFTHEN as code C'107,108' is evaluated at the end, so is it true that if any record with 108 is present, then always the GRP CD would be 108 in the final record or it might give unpredictable result?
stat
 
Posts: 3
Joined: Wed Dec 22, 2010 10:32 am
Has thanked: 0 time
Been thanked: 0 time

Re: Summing and splicing of records using ICETOOL

Postby Frank Yaeger » Thu Dec 23, 2010 11:07 pm

If EQUALS is in effect, then the first record for each key and date will be kept. If NOEQUALS is in effect, then any record for each key and date could be kept.

So for example, if you used:

  OPTION ZDPRINT,EQUALS                                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(25:C'000000')),       
   IFTHEN=(WHEN=(18,3,SS,EQ,C'103,104'),                 
    OVERLAY=(25:22,2)),                                 
   IFTHEN=(WHEN=(18,3,SS,EQ,C'105,106'),                 
    OVERLAY=(27:22,2)),                                 
   IFTHEN=(WHEN=(18,3,SS,EQ,C'107,108'),                 
    OVERLAY=(29:22,2))                                   
   SORT FIELDS=(1,5,CH,A,7,10,CH,D)                     
   SUM FIELDS=(25,2,ZD,27,2,ZD,29,2,ZD)                 
   OUTREC BUILD=(1,16,18:25,2,21:27,2,24:29,2,X,18,3)   


SORTOUT would have:

AAAAA 2009-04-09 30 50 00 103 
AAAAA 2009-02-06 60 90 00 104 
AAAAA 2008-10-27 20 70 80 104 


If you want something else for output, tell me the rules for what you want.
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: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: Summing and splicing of records using ICETOOL

Postby stat » Fri Dec 24, 2010 6:48 am

Thank you sir. My piece of work demanded to have the group code 103/104 to be used if diff Grp code is present in more than one records on same date.
So per your advice I will sort the records to have 103/104 rows first and then will use the EQUALS to get the desired result.

I really appreciate your help.
stat
 
Posts: 3
Joined: Wed Dec 22, 2010 10:32 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post