View previous topic :: View next topic |
Author |
Message |
Max Beginner
Joined: 09 Jul 2003 Posts: 5 Topics: 2
|
Posted: Wed Jul 09, 2003 7:52 pm Post subject: Output highest value for a given field |
|
|
I am writing an easytrieve program and would appreciate it if someone could advise me on how to generate a report with only the hightest( or maximum) for a given field.
Example
Field 1 Field 2
A 1
A 2
A 3
A 4
Is there a function or an easytrieve statement that I can use that will return the values below in my report:
Field1 Field2
A 4 |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
|
Posted: Wed Jul 09, 2003 8:13 pm Post subject: |
|
|
Max,
You can use the easytrive's keyword Last-dup and write out only the last duplicate.If your input file has always duplicates the following code will give you the last duplicate
Code: |
FILE FILEIN
KEY-REC 01 01 A
FILE FILEOUT FB(0 0)
***********************************************************************
* MAINLINE *
***********************************************************************
JOB INPUT (FILEIN KEY (KEY-REC))
IF LAST-DUP FILEIN OR NOT DUPLICATE FILEIN
PUT FILEOUT FROM FILEIN
END-IF
|
why not use sort products to get the last duplicate ???
Hope this helps...
cheers
kolusu |
|
Back to top |
|
|
Max Beginner
Joined: 09 Jul 2003 Posts: 5 Topics: 2
|
Posted: Wed Jul 09, 2003 9:59 pm Post subject: |
|
|
Kolusu,
You are champ! The last-dup statement returned the desired output.
On another note, in the scenario below, the last-dup statement will only return the last duplicate entry which is not necessarily the latest update for that category. What would the be the best option to pickup the latest update for each category.
Field
Loan
Category
Update Date
FILEA
Fields:
Loan|Category|UpdateDate
Values:
1|A|June 6
1|A|June 8
1|A|June 7
1|B|June 8
1|B|June 6
1|B|June 7
*** The last-dup FILEA statement will return 1|B|June 7 as the latest update. What statement would I need to use to return only the latest updates for each category:
1|A|June 8
1|B|June 8
Appreciate your help |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
|
Posted: Thu Jul 10, 2003 5:20 am Post subject: |
|
|
Max,
You really cannot find out the latest updated entry as the month is spelled out. you can use a SORT verb in easytrieve so as to sort the input
Code: |
SORT FILEIN TO FILEIN USING (LOAN, CATEGORY UPDATEDATE D)
|
You can then use the last-dup keyword to get the latest update. since your month is spelled out you really cannot sort the file.
There is a simple way of getting the latest update using SORT UTILITIES.Let me know if you are interested in that.
Hope this helps...
cheers
kolusu |
|
Back to top |
|
|
Max Beginner
Joined: 09 Jul 2003 Posts: 5 Topics: 2
|
Posted: Sat Jul 12, 2003 5:43 am Post subject: |
|
|
Kolusu,
That would be really great if you can let me know a bit about the sort utilities. Also,
if my example above used a valid date format eg 030701(yymmdd) can I still utilise the last-dup statement to grab the lates t update date? I think the file Im reading contains an update date and time and they both are 4 P
Thanks.... |
|
Back to top |
|
|
Frank Yaeger Sort Forum Moderator
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
|
Posted: Sat Jul 12, 2003 9:39 am Post subject: |
|
|
Max,
You can use a DFSORT/ICETOOL job like this to get the latest update for each loan and category:
Code: |
//S2 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
1|A|03/06/06
1|A|03/07/01
1|A|03/06/21
1|B|03/07/08
1|B|03/07/02
1|B|03/06/30
1|C|03/06/25
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=... output file
//TOOLIN DD *
* Sort by loan, category and update date
SORT FROM(IN) TO(T1) USING(CTL1)
* Get last (latest) date for each loan and category
SELECT FROM(T1) TO(OUT) ON(1,1,CH) ON(3,1,CH) LAST
/*
//CTL1CNTL DD *
SORT FIELDS=(1,1,CH,A,3,1,CH,A,5,8,CH,A)
/*
|
The result in OUT would be:
Code: |
1|A|03/07/01
1|B|03/07/08
1|C|03/06/25
|
If you only want the latest update for duplicates, you can substitute LASTDUP for LAST in the SELECT statement above. The result in OUT would then be:
Code: |
1|A|03/07/01
1|B|03/07/08
|
For complete information on DFSORT's ICETOOL, see:
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICECA109/6.0?DT=20020722140254
For lots of information on DFSORT/ICETOOL, see:
http://www.storage.ibm.com/software/sort/mvs/srtmprd.html _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort |
|
Back to top |
|
|
Max Beginner
Joined: 09 Jul 2003 Posts: 5 Topics: 2
|
Posted: Mon Jul 14, 2003 12:38 am Post subject: |
|
|
Frank,
Thanks for your help. My job has completed successfully. |
|
Back to top |
|
|
|
|