Reorder Text File and calculate totals

[andr3w]

Hi,

I have a report from our POS system which I have managed to convert into a tab seperated file with only the relavant fields listed.

My next problem is I need to relist the file so the first field is in numerical sequence from lowest to highest. I also have an issue where some of the postings are split across mutiple lines due to the bill having multiple payments. For example;

 956  03/08/14 08:28  85.00
 957  03/08/14 08:30  5.50
 959  03/08/14 08:57  25.50
 968  03/08/14 10:20  29.00
 970  03/08/14 10:23  46.00
 973  03/08/14 10:50 13.50 
 963  03/08/14 10:37  76.50
 964  03/08/14 10:38  204.50
 972  03/08/14 10:49  93.00
 973  03/08/14 10:50  42.30
 

When Im reordering the file by the first number I also need to check if there are more transactions for that same number and then add together the price field so the transaction is only written once with the correct total.

So for the line with 973 I would need to add together the 13.50 price with 42.30 and then write the line just once with the correct total field of 55.80.

Im not sure how I should do this I have attached a sample text file of data and I'm hoping someone can explain the best method to achieve the result I require.

Thanks.

sample.txt

[JohnOne]

_FileReadToArray()

_ArraySort()

_ArrayDisplay()

are a few functions to get you started. read about them in the help file and play around

[andr3w]

Hi John,

I had a quick play with these commands but I wasnt sure how I would get the arraysort to only target the first transaction number rather then the whole line.

Would I need to do something like a stringsplit to extract just the transaction number and then sort on these?

Thanks.

[mikell]

?

#include <File.au3>
#include <Array.au3>

Local $myfile = "sample.txt"
Local $array
_FileReadToArray($myfile, $array, 0, @TAB)
; _ArrayDisplay($array)
Redim $array[UBound($array)][UBound($array, 2)+1]   ; add a col 4 for count
For $i = 0 to UBound($array)-1
      $array[$i][0] = Number($array[$i][0])  ; to allow numeric sorting
      $array[$i][4] = 1
Next
_ArraySort($array)     ; sort the array by numbers in 1st column
For $i = UBound($array)-2 to 0 step -1   
   If $array[$i][0] = $array[$i+1][0] Then     ; if duplicate number found
       $array[$i][3] = StringFormat("%.2f", $array[$i][3] + $array[$i+1][3])   ; add values in col 3
       $array[$i][4] += $array[$i+1][4]     ; add the count(s)
       _ArrayDelete($array, $i+1)
  EndIf
Next
 _ArrayDisplay($array)   ; enjoy

Edited August 28, 2014 by mikell

[kylomas]

andr3w,

Check out transaction # 1036.  Looks like dups.  Is this intentional or does it need to be handled in code?

[mikell]

kylomas,

There are many dups in this sample.txt  which seems built for testing purpose only 

[kylomas]

Yes, that's why I asked the question.

[andr3w]

Mikell has nailed it!!!

Thanks so much