Sort data by date and then search by column

samrat_dutta · May 16, 2013, 9:28pm

Hi,
I have a file where data is pipe separated.First i want to sort the file content by date . Then i want to pick up the records based on the first column which should be unique and not have duplicates.

First i would like to sort by date and get the latest ones. Secondly there should be no duplicates and hence my output should be like this :

Any help with the relevant script is requested.

spacebar · May 16, 2013, 11:37pm

You can sort the file to get the order you want and remove duplicates:

$ cat t
NYSE|yyyrrrddd|toronto|isin|ticker|2013-05-15
UKS|yyyrrrddd|toronto|isin|ticker|2013-05-14
BSE|yyyrrrddd|toronto|isin|ticker|2013-05-13
HKS|yyyrrrddd|toronto|isin|ticker|2013-05-14
TSX|yyyrrrddd|toronto|isin|ticker|2013-05-16
NYSE|yyyrrrddd|toronto|isin|ticker|2013-05-15
BSE|yyyrrrddd|toronto|isin|ticker|2013-05-15

$ sort -u -t'|' -k6,6r -k1,1 t
TSX|yyyrrrddd|toronto|isin|ticker|2013-05-16
BSE|yyyrrrddd|toronto|isin|ticker|2013-05-15
NYSE|yyyrrrddd|toronto|isin|ticker|2013-05-15
HKS|yyyrrrddd|toronto|isin|ticker|2013-05-14
UKS|yyyrrrddd|toronto|isin|ticker|2013-05-14
BSE|yyyrrrddd|toronto|isin|ticker|2013-05-13

What else do you want to do?

samrat_dutta · May 17, 2013, 10:23am

I have kept the command you mentioned inside a .sh file and upon execution i got the below 2 message:

cat: cannot open t
sort: can't stat t: No such file or directory

1) below is my .sh file content where abc.psv is file which has the | data

cat t /common/PDS/abc.psv
sort -u -t'|' -k6,6r -k1,1 t /common/PDS/abc.psv

[/CODE]

2) the abc.psv file is kept in the path /common/PDS/