BlackFlag Posted April 3, 2015 Share Posted April 3, 2015 I am not looking for any code here, just want to see if the method I am thinking about using makes sense. The situation: I have 800+ plus customers that have a list of navigation charts they have purchased from us for use with our software. In each customer's account, there is a text file that lists the charts they own. I need to take this information and create a list of charts with the number of vessels that own that chart. For example, Vessel A has chart number 12345. Vessel B has chart 54321. Vessel C has chart 12345, 54321,11111. At then end of processing I want to generate a report like this: Chart Number Number of owners 11111 1 12345 2 54321 2 My plan Create a 2d array consisting of all possible chart numbers (ChartNo, number of owners) Create an array of the chartlist of a vessel Compare the chartlist array with the all possible charts array, where there are matchs, incurment all possible (number of owners) for that chart number by one. Does this make sense or should I be looking a different method. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted April 3, 2015 Moderators Share Posted April 3, 2015 BlackFlag, 800+ plusThat number of elements cries out "SQLite database" to me - and of course there is a very nice SQLite library in AutoIt, plus a number of very helpful SQLite gurus to help when needed. M23 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Zedna Posted April 3, 2015 Share Posted April 3, 2015 (edited) Liike Melba said. This kind of tasks is "peace of cake" in SQLite. I strongly recommend this way ... Edited April 3, 2015 by Zedna Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
iamtheky Posted April 3, 2015 Share Posted April 3, 2015 (edited) Its not terrible without sqllite, its all about that 2d array. Since Who owns the chart doesnt matter, only the number and how many times that number occurs, if you can get them in their own column you would have an abundance of ways of accomplishing the task. I think this would be one: expandcollapse popup#include<Array.au3> Global $aArr[9][2] $aArr[0][0] = "Vessel A" $aArr[0][1] = "12345" $aArr[1][0] = "Vessel B" $aArr[1][1] = "54321" $aArr[2][0] = "Vessel C" $aArr[2][1] = "12345" $aArr[3][0] = "Vessel C" $aArr[3][1] = "54321" $aArr[4][0] = "Vessel C" $aArr[4][1] = "11111" $aArr[5][0] = "Vessel D" $aArr[5][1] = "12345" $aArr[6][0] = "Vessel D" $aArr[6][1] = "54321" $aArr[7][0] = "Vessel D" $aArr[7][1] = "22222" $aArr[8][0] = "Vessel D" $aArr[8][1] = "12345" _ArrayDisplay(_CountItemByCol($aArr , 0)) _ArrayDisplay(_CountItemByCol($aArr , 1)) Func _CountItemByCol($aArray , $Column) Local $aOut[0][2] For $i = ubound($aArray) - 1 to 0 step - 1 $aMatch = _ArrayFindAll($aArray , $aArray[$i][$Column] , 0 , 0 , 0 , 0 , $Column) $sOut = $aArray[$i][$Column] & "|" & ubound($aMatch) _ArrayAdd($aOut , $sOut) _ArrayDelete($aArray , _ArrayToString($aMatch , ";")) $i-= ubound($aMatch) - 1 Next Return $aOut EndFunc Edited April 3, 2015 by boththose ,-. .--. ________ .-. .-. ,---. ,-. .-. .-. .-. |(| / /\ \ |\ /| |__ __||| | | || .-' | |/ / \ \_/ )/ (_) / /__\ \ |(\ / | )| | | `-' | | `-. | | / __ \ (_) | | | __ | (_)\/ | (_) | | .-. | | .-' | | \ |__| ) ( | | | | |)| | \ / | | | | | |)| | `--. | |) \ | | `-' |_| (_) | |\/| | `-' /( (_)/( __.' |((_)-' /(_| '-' '-' (__) (__) (_) (__) Link to comment Share on other sites More sharing options...
kylomas Posted April 4, 2015 Share Posted April 4, 2015 (edited) BlackFlag, Can you give an example of exactly what the input(s) are? kylomas edit: Also, I'm assuming that an account can have more than one vessel Not applicable to this task Edit2: Rough example using SQLite. Input file is generated by the script in whatever directory you run the script from. expandcollapse popup#include <array.au3> #include <sqlite.au3> local $st = timerinit() _generate_test_file() ConsoleWrite('! Time to generate test file = ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF) Local $DLL = _SQLite_Startup() If @error Then Exit MsgBox(16, 'ERROR', 'Error on SQLite Startup') Local $hDB = _SQLite_Open() If Not $hDB Then _fini() OnAutoItExitRegister('_fini') ConsoleWrite("! SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF & '! Using DLL = ' & $DLL & @CRLF) ; create chart table If _SQLite_Exec($hDB, 'create table if not exists Charts (Chart) ;') <> $SQLITE_OK Then Exit MsgBox(16, '', _SQLite_ErrMsg) ; load chart table Local $aCharts = StringSplit(FileRead(@ScriptDir & '\charts.txt'), @CRLF, 3) $st = timerinit() _sqlite_exec($hDB, 'begin immediate transaction;') For $1 = 0 To UBound($aCharts) - 1 If StringLen(StringStripWS($aCharts[$1], 3)) = 0 Then ContinueLoop ; skip - blank line If StringIsAlpha(StringLeft(StringStripWS($aCharts[$1], 3), 1)) Then ContinueLoop ; skip - 1st position not numeric _SQLite_Exec($hDB, 'insert into Charts values(' & _X(StringStripWS($aCharts[$1], 3)) & ');') Next _sqlite_exec($hDB, 'commit;') ConsoleWrite('! ' & _SQLite_TotalChanges($hDB) & ' entries added to DB in ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF) Local $arows, $irows, $icols ; list all charts usage _SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart ;', $arows, $irows, $icols) _arraydisplay($arows,'Usage Of All Charts') ; list only charts used 2 or more times _SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart having count(chart) > 1 ;', $arows, $irows, $icols) _ArrayDisplay($arows,'Chart Usage Count > 1') Func _X($str) Return _SQLite_FastEscape($str) EndFunc ;==>_X Func _fini() _SQLite_Close($hDB) _SQLite_Shutdown() Exit EndFunc ;==>_fini func _generate_test_file() local $hfl = fileopen(@scriptdir & '\charts.txt',2), $str if $hfl = -1 then exit msgbox(16,'','File open error') for $1 = 65 to 90 $str &= 'Vessel_' & chr($1) & @crlf & @crlf for $2 = 1 to random(20,50,1) $str &= @tab & stringformat('%05i',random(1,800,1)) & @CRLF Next $str &= @CRLF Next filewrite($hfl, $str) fileclose($hfl) endfunc Edited April 4, 2015 by kylomas 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...
kylomas Posted April 4, 2015 Share Posted April 4, 2015 (edited) BlackFlag, Here's a little better example using SQLite for your problem... expandcollapse popup#include <array.au3> #include <sqlite.au3> local $st = timerinit() _generate_test_file() ConsoleWrite('! Time to generate test file = ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF) ; start SQLite, open "memory" DB and set exit function Local $DLL = _SQLite_Startup() If @error Then Exit MsgBox(16, 'ERROR', 'Error on SQLite Startup') Local $hDB = _SQLite_Open() If Not $hDB Then _fini() OnAutoItExitRegister('_fini') ConsoleWrite("! SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF & '! Using DLL = ' & $DLL & @CRLF) ; create chart table If _SQLite_Exec($hDB, 'create table if not exists Charts (Chart) ;') <> $SQLITE_OK Then Exit MsgBox(16, '', _SQLite_ErrMsg) ; read chart file to array Local $aCharts = StringSplit(FileRead(@ScriptDir & '\charts.txt'), @CRLF, 3), $SQL ; load charts to DB $st = timerinit() _sqlite_exec($hDB, 'begin immediate transaction;') ; not significant for in memory DB but useful if writing DB to DASD For $1 = 0 To UBound($aCharts) - 1 If StringLen(StringStripWS($aCharts[$1], 3)) = 0 Then ContinueLoop ; skip - blank line If StringIsAlpha(StringLeft(StringStripWS($aCharts[$1], 3), 1)) Then ContinueLoop ; skip - 1st significant character not numeric $SQL &= 'insert into charts values (' & stringstripws($aCharts[$1],3) & ');' & @CRLF Next _SQLite_Exec($hDB, $SQL) _sqlite_exec($hDB, 'commit;') ; not significant for in memory DB but useful if writing DB to DASD ConsoleWrite('! ' & _SQLite_TotalChanges($hDB) & ' entries added to DB in ' & round(timerdiff($st)/1000,2) & ' seconds' & @CRLF) Local $arows, $irows, $icols ; list all charts usage _SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart ;', $arows, $irows, $icols) _arraydisplay($arows,'Usage Of All Charts') ; list only charts used 2 or more times _SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart having count(chart) > 1 ;', $arows, $irows, $icols) _ArrayDisplay($arows,'Chart Usage Count > 1') ; list highest used chart _SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart order by count(chart) desc limit 1;', $arows, $irows, $icols) _ArrayDisplay($arows,'Most Used Chart') ; list top 10 used charts _SQLite_GetTable2d($hDB, 'select chart, count(chart) as [Times Used] from charts group by chart order by count(chart) desc limit 10 ;', $arows, $irows, $icols) _ArrayDisplay($arows,'Top 10 Used Charts') Func _fini() _SQLite_Close($hDB) _SQLite_Shutdown() Exit EndFunc ;==>_fini func _generate_test_file() local $hfl = fileopen(@scriptdir & '\charts.txt',2), $str if $hfl = -1 then exit msgbox(16,'','File open error') for $1 = 65 to 90 $str &= 'Vessel_' & chr($1) & @crlf & @crlf for $2 = 1 to random(20,50,1) $str &= @tab & stringformat('%05i',random(1,800,1)) & @CRLF Next $str &= @CRLF Next filewrite($hfl, $str) fileclose($hfl) endfunc The input file is generated in the script. I imagine that your input will be different but similar based on your description. Just change the DB load routine to accommodate whatever your input looks like, or, format your input as I have. There is a way to change the SQL "insert" stmt to "chain" values but I didn't bother with it. As is, the code will load 90,000+ DB entries in around 1.5 seconds. If volume / performance become an issue then post back. kylomas Edited April 4, 2015 by kylomas 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...
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