Sort/Sum help



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

Sort/Sum help

Postby D9988 » Thu Feb 18, 2010 2:56 am

I'm having trouble with the sort/sum feature. I have an input dataset of transactions that is already sorted by account(1,5,PD,A), payment type(14,1,CH,A), and transaction date(6,8,CH,D). (The input file was sorted via SORT FIELDS=(1,5,PD,A,14,1,CH,A,6,8,CH,D)). All of these transactions are for the same account. There are 2 payments types (A and P), which are sorted by date descending.

----+----1----+----2----+----3
 -----------------------------
...ø.20060201APP .....<..... 
03170FFFFFFFFCDD400033400000 
9810F20060201177000008C0000F 
 -----------------------------
...ø.20060201APP ...ÅÁ...... 
03170FFFFFFFFCDD400066200000 
9810F20060201177000075C0000F 
 -----------------------------
 -----------------------------
...ø.20011101APP ....àð..... 
03170FFFFFFFFCDD400004800000 
9810F20011101177000014C0000F 
 -----------------------------
...ø.20010803APP ....øð..... 
03170FFFFFFFFCDD400017800000 
9810F20010803177000050C0000F 
 -----------------------------
...ø.20060301PU A.....ð..... 
03170FFFFFFFFDE4C00012800000 
9810F20060301740100013C0000F 
 -----------------------------
...ø.20060301PP A.....ý..... 
03170FFFFFFFFDD4C00012800000 
9810F20060301770100013D0000F 
 -----------------------------
...ø.20060201PP  .....<..... 
03170FFFFFFFFDD4400002400000 
9810F20060201770000197C0000F 
 -----------------------------
...ø.20011201PU S.....¸..... 
03170FFFFFFFFDE4E00000900000 
9810F20011201740200000D0000F 
 -----------------------------
...ø.20011102PU A.....æ..... 
03170FFFFFFFFDE4C00000900000 
9810F20011102740100000C0000F 
 -----------------------------



What I want to do is sum the "A" payment types and the "P" payment types, keeping the most recent transaction date for each payment type. I am able to succesfully sum the transaction amounts (18,6,PD) for each type, but sometimes the wrong date is written to the output file. In the above example, I expect it to keep the 20060201date for the A type, and 20060301 for the P type. I am using the following sort commands:

//SYSIN     DD *                   
  SORT FIELDS=(1,5,PD,A,14,1,CH,A),
  FILSZ=E5000000                   
  SUM FIELDS=(18,6,PD)


Here is my output dataset. It grabs the most recent date for the A type correctly, but for some reason it grabs an older date for the P type (20011201):

=COLS> ----+----1----+----2----+---
------------------------------------
574929  ø 20060201APP   &áæ 
       03170FFFFFFFFCDD400154900000
       9810F20060201177000205C0000F
------------------------------------
574930  ø 20011201PU S  /Ñ@ 
       03170FFFFFFFFDE4E00166700000
       9810F20011201740200219C0000F
------------------------------------


Any ideas?? Please let me know if you need any more info. Thank you.
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort/Sum help

Postby Frank Yaeger » Thu Feb 18, 2010 3:15 am

I'd guess that you have NOEQUALS in effect which means that the duplicate records can be output in any order.

Since your records are already sorted in the order you want, to ensure that you get the first record for each set of dups, you must have EQUALS in effect. Try adding this to SYSIN:

   OPTION EQUALS
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: Sort/Sum help

Postby D9988 » Thu Feb 18, 2010 3:25 am

I'll give this a try, thank you Frank :D
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort/Sum help

Postby D9988 » Fri Feb 19, 2010 2:43 am

That took care of it, thank you!
David
D9988
 
Posts: 34
Joined: Fri Nov 09, 2007 10:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sort/Sum help

Postby Frank Yaeger » Fri Feb 19, 2010 4:20 am

Glad I could help.
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