|
View previous topic :: View next topic
|
| Author |
Message |
sguhan
New User
Joined: 15 Mar 2010
Posts: 9
Location: Chennai
|
|
|
|
I have a scenario wherein i have to extract records from a file based on the maximum occurence of particular field for a given combination of key.
Can this be achieved by using DFSORT? RECFM=FB,RECL=80
Example: Input:
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/23/2010 Ragu
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/05/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx3 04/01/2010 prav
Output:
1111111111111xxxx1 04/24/2010
2222222222222xxxx2 04/01/2010
2222222222222xxxx3 04/01/2010
For the key '1111111111111xxxx1 ' , date '04/24/2010' has the maximum occurence. so the output should have the key and the maximum occuring date.There should be only one occurence for a particular key. Please advise. |
|
| Back to top |
|
 |
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| Quote: |
2222222222222xxxx2 04/01/2010
|
The maximum date here would be 04/05/2010. Given that you just made a mistake and that's what you really want, here's a DFSORT/ICETOOL job that will do what you asked for:
| Code: |
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=... input file (FB/80)
//OUT DD DSN=... output file (FB/80)
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,18,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
SORT FIELDS=(1,18,CH,A,27,4,CH,D,21,5,CH,D)
OUTFIL FNAMES=OUT,BUILD=(1,30)
/*
|
OUT would have:
| Code: |
1111111111111xxxx1 04/24/2010
2222222222222xxxx2 04/05/2010
2222222222222xxxx3 04/01/2010
|
|
|
| Back to top |
|
|
sguhan
New User
Joined: 15 Mar 2010
Posts: 9
Location: Chennai
|
|
|
|
Frank,
Thanks for your reply.
I have not made a mistake. I want the date field to be appear in the output, which has more number of occurrence for the given key, not the maximum value of date for a particular key. |
|
| Back to top |
|
|
sguhan
New User
Joined: 15 Mar 2010
Posts: 9
Location: Chennai
|
|
|
|
Frank,
Date fields will also be in sorted order in the inout file...
Example:
1111111111111xxxx1 04/23/2010 Ragu
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx2 04/05/2010 raju
2222222222222xxxx3 04/01/2010 prav
2222222222222xxxx3 04/01/2010 prav |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| Quote: |
Example:
1111111111111xxxx1 04/23/2010 Ragu
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
1111111111111xxxx1 04/24/2010 Arun
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx2 04/05/2010 raju
2222222222222xxxx3 04/01/2010 prav
2222222222222xxxx3 04/01/2010 prav |
Small questions please?
Your output would contain three records?
The first output:
1111111111111xxxx1 04/24/2010 Arun
This because there are more Aruns than Ragas?
But which one for the second:
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 raju
2222222222222xxxx2 04/01/2010 Raju
2222222222222xxxx2 04/05/2010 raju
Is the capitalized Raju the same as the non-capitalized raju?
In either case, which date if there are different dates such as 04/05 and 04/01?
Or with multiple date (since they are already sorted), the first or last?
And the third:
2222222222222xxxx3 04/01/2010 prav |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
| Back to top |
|
|
sguhan
New User
Joined: 15 Mar 2010
Posts: 9
Location: Chennai
|
|
|
|
It is working.
Thanks for your help. |
|
| Back to top |
|
|
|
|
