|
View previous topic :: View next topic
|
| Author |
Message |
Naveen.katkuri24
New User
Joined: 26 Sep 2013
Posts: 3
Location: India
|
|
|
|
Hi All,
I have been a silent follower of this forum and I have used lot of tips and resolutions posted by seniors on the different topics.
I just came across one scenario. I tried to search the forum for the same, but could not find( May be I have not used the exact word to search the forums). So posting it here.
I have an input file with below fields. When the file has duplicate keys, then It should process only the last key record in the output as shown below.
| Code: |
Key Ref No Last Changed Date
AAAA 7 2017-08-14
AAAA 7 9999-12-31
AAAA 8 9999-12-31 --> output
BBBB 2147483647 9999-12-31 --> output
CCCC 3 2012-06-19
CCCC 2 2012-06-16 --> output
DDDD 4 2017-06-10
DDDD 2 2016-09-17
DDDD 3 2018-10-09 --> output
EEEE 7 1998-01-20
EEEE 6 2000-02-28 --> output |
Can any one please let me know if we can achieve this using SORT ?
Thanks in Advance.
Regards,
Naveen |
|
| Back to top |
|
 |
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
Hi,
I'd suggest looking at ICETOOLs' SELECT LAST or SELECT LASTDUP.
Garry. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2021
Location: USA
|
|
|
|
| Code: |
OUTFIL NODETAIL,REMOVECC,
SECTIONS=(1,5,
TRAILER3=(full record positions)) |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
| Back to top |
|
|
Naveen.katkuri24
New User
Joined: 26 Sep 2013
Posts: 3
Location: India
|
|
|
|
I have used the below code.
| Code: |
//IN DD *
AAAA 7 2017-08-14
AAAA 7 9999-12-31
AAAA 8 9999-12-31
BBBB 2147483647 9999-12-31
CCCC 3 2012-06-19
CCCC 2 2012-06-16
DDDD 4 2017-06-10
DDDD 2 2016-09-17
DDDD 3 2018-10-09
EEEE 7 1998-01-20
EEEE 6 2000-02-28
FFFF 4 2010--08-31
GGGG 2 2012-03-13
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(2,6,PD) LASTDUP
/*
OUTPUT:
AAAA 8 9999-12-31
CCCC 2 2012-06-16
DDDD 3 2018-10-09
EEEE 6 2000-02-28
|
The above code is ignoring unique key records and processing only dup keys.
But my input file may contain unique and non-unique keys in the input file.
If there are non-unique records, then it should process last dup key along with unique key in the output. So could you please let me know if there is any other way to achieve this. |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Click HERE see if the manuals that you could have read help you.
I found what you want in less than a minute  |
|
| Back to top |
|
|
Naveen.katkuri24
New User
Joined: 26 Sep 2013
Posts: 3
Location: India
|
|
|
|
At last achieved my desired result with the below code.
OPTION EQUALS
SORT FIELDS=COPY
OUTFIL NODETAIL,REMOVECC,
SECTIONS(1,5,TRAILER3=(1,30))
Thanks a lot for your valuable inputs. especially @expat. |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
You didn't try:
| Code: |
| SELECT FROM(IN) TO(OUT) ON(2,6,PD) LAST |
which I think is more readable than the solution you have.
Garry |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Expat, Good one 
Naveen, You got to open up those links I shared instead to overlook them and get to learn learn by yourself how it works. |
|
| Back to top |
|
|
|
|
