n3wbie Posted July 29, 2015 Share Posted July 29, 2015 (edited) Hello M new To Forum But Using Autoit over a year I Was Stuck When I Wanted To Group Down Elements Of Array in Autoit Than Came Across This Post https://www.autoitscript.com/forum/topic/42172-group-elements-in-array/#comment-314397 But There wasnt any reply Probably I will Re explain Scenerio Suppose i Have a data where in there are 5 teams Red Blue Green yellow ... and data table consist of data like name of person and much more details Now Basically I Want data to be consolidated To Teams Statistics Like Team a has 10 Participants, average age of participants etc For This Mysql Has A Command Group by Like I could Do It 'Select Count(*) ,avg(age) from table group by team_name Here There Is Scenario wherein I Know That There Are 5 teams But In My Data I Dont So I Want To Group It Can U Please Help Me Sort It Down Also If You Cant Understand My Problem I Have Attached An Example Spreadsheet Which Elaborates Whats Input And Output I Require Just Thing Is I Dont Want Excel To Get Involved In This example.xlsx Edited July 29, 2015 by n3wbie Link to comment Share on other sites More sharing options...
JohnOne Posted July 29, 2015 Share Posted July 29, 2015 Well there is _ArraySort, but you would have to create your own additional sorting functions with the criteria you specifically need.Personally I'd use sqlite instead. 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...
n3wbie Posted July 29, 2015 Author Share Posted July 29, 2015 Sqllite Ok Havnt Tried It Will Look Into But Can This Type Of Sorting Be Done There ?And Do I Need To Install Some Additional Things In Order To Work With SqlLite Or It Works with Autoit standalone? Link to comment Share on other sites More sharing options...
JohnOne Posted July 29, 2015 Share Posted July 29, 2015 SQLite is generally on most windows machines, and is standalone database which will work with most SQL like statements. I believe it is downloaded if not present on users machine.Just look in helpfile on index tab for _SQLite* functions. 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...
jchd Posted July 29, 2015 Share Posted July 29, 2015 Look at _SQLite_* functions in help: zero installation and zero maintenance.Yes, SQLite is a pretty useful embedded (i.e. not client-server) SQL engine which offers most of SQL features, of course including group by, order by, aggregate functions, and much more. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
n3wbie Posted July 29, 2015 Author Share Posted July 29, 2015 Thanks Guys For Prompt Reply'sBut Still M Thinking Doing It Not Involving sqllite But I Will Work Down The Only Problem That Arises For Me Using Sql Lite Is ITs A File Based Database Ie It isnt A object Oriented So It Slows Down If More Records Are To be SortedSo If You have Any ALternative Please Suggest My Records Would Have Min 6000 Records And Max 8000SO if any other solution Please Do Suggest Link to comment Share on other sites More sharing options...
jchd Posted July 29, 2015 Share Posted July 29, 2015 6000 rows is a ridiculously small DB; 6 million rows is average, 6 billions rows is largish.Contrary to what you think, an SQLite DB can be memory- or disk-based.I also don't understand why you believe that being object-oriented anyhow helps efficiency over scaling.(Can you please stop using emphasis uppercase, that makes your posts hard to read.) This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Gianni Posted July 29, 2015 Share Posted July 29, 2015 you can play a bit with both ways, that is: by using only _Array* functions or by involving Sqlite to our rescue. Here two examples to play with:1) By using only _Array* functions (basically _ArrayUnique() and _ArrayFindAll() )expandcollapse popup#include <array.au3> local $aInput[27][4] = [['Name', 'Team', 'Age', 'Salary'], _ ['A', 'Blue', '41', '98658'], _ ['B', 'Red', '40', '54406'], _ ['C', 'Green', '33', '74489'], _ ['D', 'Yellow', '36', '57857'], _ ['E', 'Red', '34', '55565'], _ ['F', 'Blue', '33', '83666'], _ ['G', 'Yellow', '44', '66627'], _ ['H', 'Red', '40', '63970'], _ ['I', 'Red', '49', '89346'], _ ['J', 'Blue', '37', '79437'], _ ['K', 'Red', '41', '79038'], _ ['L', 'Yellow', '31', '84754'], _ ['M', 'Red', '46', '96611'], _ ['N', 'Blue', '43', '86699'], _ ['O', 'Red', '32', '94452'], _ ['P', 'Red', '39', '77050'], _ ['Q', 'Yellow', '31', '76837'], _ ['R', 'Blue', '45', '59680'], _ ['S', 'Red', '50', '72844'], _ ['T', 'Blue', '42', '90642'], _ ['U', 'Red', '48', '83451'], _ ['V', 'Blue', '38', '60356'], _ ['W', 'Red', '37', '70999'], _ ['X', 'Yellow', '46', '70282'], _ ['Y', 'Red', '47', '69867'], _ ['Z', 'Red', '48', '57109']] _ArrayDisplay($aInput, "Input as array") Local $ii $aUniques = _ArrayUnique($aInput, 1, 1, 0, 0) Local $aOutputs[UBound($aUniques) + 1][UBound($aInput, 2)] ; make room for output data For $i = 0 to UBound($aUniques) - 1 ; scan all uniques $ii = $i + 1 ; do not use row 0 of $aOutputs (shift all rows by 1), row 0 will be used for titles of columns $aIndexes = _ArrayFindAll($aInput, $aUniques[$i], 1, 0, 0, 0, 1) ; rows of $aInput containing only the unique nr. $i $aOutputs[$ii][1] = $aInput[$aIndexes[0]][1] ; the name of this unique goes to column 1 of $aOutputs for $i2 = 0 to UBound($aIndexes) - 1 ; scan all rows containing this "team" value $aOutputs[$ii][2] += $aInput[$aIndexes[$i2]][2] ; sum all ages (column 2) $aOutputs[$ii][3] += $aInput[$aIndexes[$i2]][3] ; sum all salary (column 3) Next $aOutputs[$ii][2] = Round($aOutputs[$ii][2] / UBound($aIndexes), 5) ; average of ages Next ; next group of uniques for $i = 0 to UBound($aInput, 2) - 1 ; titles of columns $aOutputs[0][$i] = $aInput[0][$i] Next _ArrayDisplay($aOutputs, "Outputs 1 & 2")2) by using an udf that will facilitate you allowing the use of SQL (nearly) directly on arrays:; by sql #include <array.au3> #include <ArraySQL.au3> ; <-- https://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/#comment-1234441 Local $aInput[26][4] = [['A', 'Blue', '41', '98658'], _ ['B', 'Red', '40', '54406'], _ ['C', 'Green', '33', '74489'], _ ['D', 'Yellow', '36', '57857'], _ ['E', 'Red', '34', '55565'], _ ['F', 'Blue', '33', '83666'], _ ['G', 'Yellow', '44', '66627'], _ ['H', 'Red', '40', '63970'], _ ['I', 'Red', '49', '89346'], _ ['J', 'Blue', '37', '79437'], _ ['K', 'Red', '41', '79038'], _ ['L', 'Yellow', '31', '84754'], _ ['M', 'Red', '46', '96611'], _ ['N', 'Blue', '43', '86699'], _ ['O', 'Red', '32', '94452'], _ ['P', 'Red', '39', '77050'], _ ['Q', 'Yellow', '31', '76837'], _ ['R', 'Blue', '45', '59680'], _ ['S', 'Red', '50', '72844'], _ ['T', 'Blue', '42', '90642'], _ ['U', 'Red', '48', '83451'], _ ['V', 'Blue', '38', '60356'], _ ['W', 'Red', '37', '70999'], _ ['X', 'Yellow', '46', '70282'], _ ['Y', 'Red', '47', '69867'], _ ['Z', 'Red', '48', '57109']] _ArrayDisplay($aInput, "Input as array") $sQuery = "Select column1,avg(column2), sum(column3) from array group by column1;" $aResult = _ArraySQL($aInput, $sQuery) _ArrayDisplay($aResult) n3wbie 1 Chimp small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt.... 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