Help in writing a KSH script to filter the latest record?

Shell Programming and Scripting
1

Hi All,

I have a text file with the folowing content.

BANGALORE|1417|2010-02-04 08:41:04.174|dob|xxx
BANGALORE|1416|2010-02-04 08:23:19.566|dob|yyy
BANGALORE|1415|2010-02-04 08:20:14.497|dob|aaa
BANGALORE|1414|2010-02-04 08:19:40.065|dob|vvv
BANGALORE|1413|2010-02-04 08:16:29.817|dob|zzz
BANGALORE|1413|2010-02-04 08:16:29.817|dob|www
BANGALORE|1413|2010-02-04 08:16:29.817|dob|rrr
BANGALORE|1413|2010-02-04 08:16:29.817|dob|JJJ
BANGALORE|1413|2010-02-04 08:16:29.817|dob|kkk
BANGALORE|1413|2010-02-04 08:16:29.817|dob|mmm
BANGALORE|1419|2010-02-04 08:17:29.817|dob|zzz
BANGALORE|1413|2010-02-04 08:16:29.817|dob|iii
BANGALORE|1413|2010-02-04 08:16:29.817|dob|nnn
BANGALORE|1418|2010-02-05 08:19:40.065|dob|vvv
BANGALORE|1413|2010-02-04 08:16:29.817|dob|ppp
BANGALORE|1413|2010-02-04 08:16:29.817|dob|ooo
BANGALORE|1429|2010-02-04 08:11:29.817|dob|zzz

I want to extract the latest (The third column can be used to identify the latest record) records in the above file for each name (the last field).
Finaly the output should be like this.

BANGALORE|1415|2010-02-04 08:20:14.497|dob|aaa
BANGALORE|1413|2010-02-04 08:16:29.817|dob|iii
BANGALORE|1413|2010-02-04 08:16:29.817|dob|jjj
BANGALORE|1413|2010-02-04 08:16:29.817|dob|kkk
BANGALORE|1413|2010-02-04 08:16:29.817|dob|mmm
BANGALORE|1413|2010-02-04 08:16:29.817|dob|nnn
BANGALORE|1413|2010-02-04 08:16:29.817|dob|ooo
BANGALORE|1413|2010-02-04 08:16:29.817|dob|ppp
BANGALORE|1413|2010-02-04 08:16:29.817|dob|rrr
BANGALORE|1418|2010-02-05 08:19:40.065|dob|vvv
BANGALORE|1413|2010-02-04 08:16:29.817|dob|www
BANGALORE|1417|2010-02-04 08:41:04.174|dob|xxx
BANGALORE|1416|2010-02-04 08:23:19.566|dob|yyy
BANGALORE|1419|2010-02-04 08:17:29.817|dob|zzz

Can any one help me to write the unix shell script to get this output?

Thanks in advance.
Karpak

2

Since the records appear to be time sorted, you could use something like this:

awk -F '|' 'END{for(i in A)print A}{A[$NF]=$0}' infile | sort -t'|' -k5,5
3

hello my friend Scrutinizer,
i test there may be sth wrong with your script because the third field is not sorted.

4

If the dates are not sorted and if you have GNU date than you could this I suppose:

#!/bin/ksh
typeset -A A
typeset -A D
while IFS="|" read city field2 tdate field4 key ; do
  newdate=$(date -d "$tdate" '+%s')
  if [ $newdate -gt "${D[$key]}" ]; then
    A[$key]="$city|$field2|$tdate|$field4|$key"
    D[$key]=$newdate
  fi
done < infile126
for i in "${A[@]}"; do
  echo "$i"
done|sort -t'|' -k5,5
5

Dear Scrutinizer,

Based on data with slight modification. Your logic works fine.

Thank you very much

Karpak