andr3w Posted August 28, 2014 Share Posted August 28, 2014 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 Link to comment Share on other sites More sharing options...
JohnOne Posted August 28, 2014 Share Posted August 28, 2014 _FileReadToArray() _ArraySort() _ArrayDisplay() are a few functions to get you started. read about them in the help file and play around AutoIt Absolute Beginners Require a serial Pause Script Video Tutorials by Morthawt ipify Monkey's are, like, natures humans. Link to comment Share on other sites More sharing options...
andr3w Posted August 28, 2014 Author Share Posted August 28, 2014 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. Link to comment Share on other sites More sharing options...
mikell Posted August 28, 2014 Share Posted August 28, 2014 (edited) ? #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 Link to comment Share on other sites More sharing options...
kylomas Posted August 28, 2014 Share Posted August 28, 2014 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 Link to comment Share on other sites More sharing options...
mikell Posted August 28, 2014 Share Posted August 28, 2014 kylomas, There are many dups in this sample.txt which seems built for testing purpose only Link to comment Share on other sites More sharing options...
kylomas Posted August 28, 2014 Share Posted August 28, 2014 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 Link to comment Share on other sites More sharing options...
andr3w Posted August 29, 2014 Author Share Posted August 29, 2014 Mikell has nailed it!!! Thanks so much Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now