To extract records from file based on the latest date and store in a new file
<p>I have a file with different balances for a particular account. And every day these balance changes. What I want to do is extract balance record of a particular account based on the latest date.</p>

<p>I am following an approach where I am adding the date into the first column of records manually using an <code>awk</code> script but taking date from the file name since I do not get date in my records. Next I want to sort the records based on the Account number and want to extract the record with latest date in another file. </p>

<p>Can anybody help me with this?</p>

<p>Till now I have written this code and I am unable to sort and extract the data into the other file</p>

<pre><code>#!/usr/bin/ksh

f=mainfile_20151201.dat
s=`echo $f | cut -c 16-23`
echo "$f -&gt; $s"

awk -F "~" 'BEGIN { OFS = "~"; ORS = "\n" ; date='$s' ; IFS = "~"} { $1=date"~"$1 ; print  }' mainfile_20151201.dat &gt; tempdate

awk -F "~" 'BEGIN { OFS = "~"; ORS = "\n" ;  IFS = "~"} { $1 ; print  }' tempdate &gt; newfile
</code></pre>

<p>Sample data: </p>

<pre><code>AccountNumber~~0~149038.40000000~149038.4~0.00000000~0.00000000~0.00000000
</code></pre>

<p>Please note that the data in the 4th field changes everyday</p>