Jump to content
Sign in to follow this  
andr3w

Reorder Text File and calculate totals

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by mikell

Share this post


Link to post
Share on other sites
kylomas

andr3w,

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
mikell

kylomas,

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

Share this post


Link to post
Share on other sites
kylomas

Yes, that's why I asked the question.


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×