Jump to content

ArrayMultiColSort -New Release 06 April 2019


Melba23
 Share

Recommended Posts

To sort numeric data you need to store data in numeric format in the column. Collation as text yields "12" < "9" just because of lexicographic order used ("1" < "9"), but gives 12 > 9 since the comparison is now made in integers (also works for negative values).

The syntax shows correctly after page refresh.

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 here
RegExp tutorial: enough to get started
PCRE 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

  • Moderators

Funtime60,

What jchd said!

So either store the elements as numbers, or if they are read in from a file as strings, you will need to loop through them using Number to convert them:

#include <Array.au3>
#include <ArrayMultiColSort.au3>

Global $testfilearray1[3][3] = [["123", "321", "-1"], ["123", "-31", "13"], ["123", "-321", "-12"]]

For $i = 0 To UBound($testfilearray1, 1) - 1
    For $j = 0 To UBound($testfilearray1, 2) - 1
        $testfilearray1[$i][$j] = Number($testfilearray1[$i][$j])
    Next
Next

Global $aSortData[][] = [[1, 0], [2, 0]]

_ArrayMultiColSort($testfilearray1, $aSortData)

_ArrayDisplay($testfilearray1, "", Default, 8)

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

My apologies, I failed to notice that my posts were the last ones on the page so my responses were delayed.

Thank you for the clarification jchd, I suspected as much.

At least my hours spent generating my padding thing (I'm still unsure of the proper name) aren't entirely in vain as It has effectively set up a method of looping through only the columns to be sorted.

Link to comment
Share on other sites

Melba23

Not to criticize, but wouldn't it be more efficient to only make the columns that are to be sorted into number format? repurposing this section of code from my padding thing should do just that. Is there anything inherently wrong doing it this way that  should Know about?

This has the same requirements as my original padding thing on the previous page.

#Region Init
Local $testfilearray1[3][3] = [["123", "321", "-1"], ["123", "-31", "13"], ["123", "-321", "-12"]]
Local $testsize = UBound($testfilearray1) - 1
Global $aSortData[][] = [[1, 0], [2, 0]]
#EndRegion Init


#Region Make Numb
Local $sorttotalcount = UBound($aSortData)-1
For $sortcount = 0 To $sorttotalcount Step 1
    Local $colnum  = $aSortData[$sortcount][0]
    For $testfilerownum2 = 0 To $testsize Step 1
        $testfilearray1[$testfilerownum2][$colnum] = Number($testfilearray1[$testfilerownum2][$colnum])
    Next
Next
_ArrayDisplay($testfilearray1, "$testfilearray1")
#EndRegion
Link to comment
Share on other sites

  • Moderators

Funtime60,

Quote

wouldn't it be more efficient to only make the columns that are to be sorted into number format?

Of course - in fact if you try to convert text string elements into numbers you will destroy them. I only looped through the whole array in my example because all columns in your original example were numeric.

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • 6 months later...
  • Moderators

Adele,

The secret is to get the sort data array correctly formatted:

#include "ArrayMultiColSort.au3"

Global $aArray[][] = [[2, 2], [2, 1], [2, 3], [1, 2], [1, 1], [1, 3]]

; Here is the original array
_ArrayDisplay($aArray, "Original", Default, 8)

; Set the order in which the columns are to be sorted and how to sort them
; First we sort col 0 in ascending order and then col 1 also in ascending order
Global $aSortData[][] = [[0, 0], [1, 0]]

; Sort the array
_ArrayMultiColSort($aArray, $aSortData)

; And here is the sorted one
_ArrayDisplay($aArray, "Sorted", Default, 8)

All clear now?

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • 3 months later...
  • Moderators

maniootek,

No - neither do I check if the user brushed their teeth last night or is wearing clean(ish) underwear.

There is a fine line between making UDFs idiot-proof (which is evidently impossible) by adding significant error-checking code, which both inflates and complicates the UDF, and leaving some things to the intelligence of the user. In this case I felt that as the user required specific columns to be sorted it was likely that they would correctly enter the column numbers in the array - which I take it you did not and so got an error. I will take a look and see how difficult it would be to check the indices in $aSortData - but do not hold your breath.

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • Moderators

maniootek,

An amended UDF with a quick sanity check of the columns to be sorted:

Please let me know what you think.

M23

Edited by Melba23
Beta code removed

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • Moderators

maniootek,

Great - I will release a new version this weekend.

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • Moderators

[New Release] - 06 April 2019

Added: Error-checking for sensible column numbers in the $aSortData array, with an additional error status.

New code and zip in the first post.

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • 4 months later...
  • 4 months later...

@Melba23: that's a useful UDF and it's fast too  :thumbsup:

I just tested it to sort a column containing digits & letters, maybe I should have done it differently but it worked :

257224292_arraymultisort.png.c69cb006b1b8e6cbb620b4ebe8fbbb37.png

#include "ArrayMultiColSort.au3" ; Melba23's UDF
#include <Array.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>

Local $aArray, $aArrayRegExp
$sFilePath = @ScriptDir & "\ArrayMultiColSort - personal example.csv"
_FileReadToArray($sFilePath, $aArray, $FRTA_NOCOUNT, ",")
If @error Then
    MsgBox($MB_TOPMOST, "_FileReadToArray", "@error = " & @error)
    Exit
EndIf

_ArrayDisplay($aArray, "1 = before multisort")

Local $iDim_Rows = UBound($aArray, $UBOUND_ROWS)
Local $iDim_Cols = UBound($aArray, $UBOUND_COLUMNS)
Redim $aArray[$iDim_Rows][$iDim_Cols + 2] ; add 2 temporary columns from the right

For $iRow = 0 To $iDim_Rows - 1
    $aArrayRegExp = StringRegExp($aArray[$iRow][0], '^\d+', $STR_REGEXPARRAYGLOBALMATCH)
    If Not @error Then ; $aArrayRegExp is valid
        $aArray[$iRow][$iDim_Cols] = Number($aArrayRegExp[0])
        $aArray[$iRow][$iDim_Cols + 1] = StringMid($aArray[$iRow][0], StringLen($aArrayRegExp[0]) + 1)
    Else
        $aArray[$iRow][$iDim_Cols] = "A"
        $aArray[$iRow][$iDim_Cols + 1] = $aArray[$iRow][0]
    EndIf
Next

; Sort on both temporary columns, ascending
Local $aSortData[][] = [ _
    [$iDim_Cols, 0], _
    [$iDim_Cols + 1, 0]]
_ArrayMultiColSort($aArray, $aSortData, 1) ; 3rd par = 1 (index to start from) as index 0 = header

Redim $aArray[$iDim_Rows][$iDim_Cols] ; delete 2 temporary columns from the right

_ArrayDisplay($aArray, "2 = after multisort")

As you can see in the script, 2 temporary columns were added from the right of the Array :
* First temporary column : populated with the digits part (or an "A" in case no digits are found)
* Second temporary column : contains the rest of the string.

Then your UDF sorts these 2 temporary columns (ascending) before they got deleted, they did the job :)
We notice that 51, 3526, 6001 are correctly sorted and they are followed by their corresponding string part (which is also correctly sorted compared to the original). Also "JANETTE" and "FAWN" which had no digits at all are now correctly placed at the end of the Array (due to the "A" while sorting)

In fact, I need to do this in a Listview when the user needs to sort this kind of "mixed" column. The problem is that it seems more complicated to sort that way in a listview which is already populated, because it requires to use GUICtrlListView_RegisterSortCallBack() and its 2nd parameter which is very slow for sorting when equal to 2 (Use Windows API StrCmpLogical : better for "x1y" < "x10y")

I wonder if there is another alternative for using your UDF in listview to sort this kind of column, because I can't delete all "native created" items one by one (it will take too much time) just to reuse their contents in an Array, do exactly what we saw in the script above, then repopulate the listview... it will be too much time consuming, depending on the number of items/columns.

Edited by pixelsearch
Changed '\d+' to '^\d+'
Link to comment
Share on other sites

  • Moderators

pixelsearch,

If you use my GUIListViewEx UDF to manage the ListView then you can use the _GUIListViewEx_UserSort function to allow columns to be sorted using any function you require. That might well be a way to do what you wish - although it does mean that you have to use the GUIListViewEx UDF to do all ListView manipulation that exists within your script, which may mean too much of a rewrite for just this one advantage.

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

Thanks Melba23, I will consider all options.
Anyway, I'm glad that I discovered your solid ArrayMultiColSort UDF, it is useful and will be re-used at 1st occasion :)

Edit : why isn't ArrayMultiColSort.au3 included into Array.au3 ?
I mean, who decides to add (or not) an UDF to the next official AutoIt release ?
imho, it should be added because it is useful, reliable, fast & small (8kb)

Edited by pixelsearch
Link to comment
Share on other sites

  • 3 weeks later...

Hi Melba23,
I achieved my goal by adding your function to the "CSV file editor" script. Natural sort uses it and it works fine now, with good speed. Using your function, I even could do what follows :

1390866826_Keepselectionaftersort.png.435b7a3403c4672970077eeebcd6a880.png

We notice that a subitem had focus before the sort... and it's still focused after the sort. It may be not an extraordinary feature but it could be handy, especially on LV having plenty of rows (or if you simply would like the cell to keep focus).

I tried to script it first by using $iParam ("a value to associate with the item") now that my items are not any more "native created". But I couldn't do it that way, then I tried with your ArrayMultiColSort() function and could do it, while keeping your function untouched. So thanks for that too :)

Edit1: Just checked the help file, topics _GUICtrlListView_*
It seems that it also could be done this way :

Local $iItemID = _GUICtrlListView_MapIndexToID($g_idListview, $g_iItem)
ConsoleWrite("$iItemID = " & $iItemID & @lf) ; selected cell in this item (before sort)

; sort function here...

Local $iItemAfterSort = _GUICtrlListView_MapIDToIndex($g_idListview, $iItemID)
ConsoleWrite("$iItemAfterSort = " & $iItemAfterSort & @lf) ; item to redraw (after sort)

Gonna confirm it now, though it already seems very promising.
Thank you, help file :thumbsup:

Edit2: tests made and both ways are needed to have the same cell selected when the sort is done.
1) Numeric & String sorts :
MapIndexToID & MapIDToIndex are ok because both Numeric & String sorts send an LVM_SORTITEMSEX message, which takes care of "changing the index of each item to reflect the new sequence". In this case MapIndexToID & MapIDToIndex are reliable.

2) Natural sort :
ArrayMultiColSort() does the job, because extracting the data from LV in an array (splitting 1 column in 2), then multi sorting this array on 2 columns, then updating each and every item/subitem text in LV doesn't involve Windows sort process at all, which means the index of each item didn't change (though LV is fully 'sorted') so MapIndexToID & MapIDToIndex aren't reliable to know where now is placed the item that had one cell selected before the sort...

Edited by pixelsearch
Link to comment
Share on other sites

  • 3 months later...

@Melba23, great work as always. Just wanted to say thanks, and share a small modification that I made, for of course my own uses.

 

I have a file list that is in a format like "prefix_filename.ext". I then have it in a 2D array like:

; [0][0] = File name
; [0][1] = Occurances of file in a search
Local $aFiles[][] = [ _
        ['pick_Rough.txt', 2], _
        ['pick_All.txt', 5], _
        ['pick_Fine.txt', 1] _
        ['choose_Rough.txt', 1] _
        ['choose_Smooth.txt', 1] _
        ['all_All.txt', 8] _
        ['all_Fine.txt', 1] _
        ]

I wanted to be able to sort by the occurrence count ([[1, 1]]), and then by a couple specific prefixes, such as the most common ('pick_'), then the most general ('all_') so that when I'm looping through the array later to find occurrences of the files, the most common ones are searched first. Your default for the string sort means that I would have had to put the full file name in an ordered list, which I didn't want to do.

I edited the __AMCS_SortChunk function line 164 from:

If $aArray[$i][$iColumn] = $aSortOrder[$j] Then

-->

If StringInStr($aArray[$i][$iColumn], $aSortOrder[$j]) > 0 Then

This way, the string sorting only needs to contain a part of what you pass in the aSortData to be sorted, so I could pass it like: 

$aSortData[][] = [[1, 1], [0, $sSort & ',all_']]

Where $sSort would be the most common/recent (like 'pick_'), followed up by the most general, like 'all_' for the file prefixes. This for me works great, and now I can sort by the most found, then by specific file prefixes.

 

Hope that all makes sense. I know that I could also do StringInStr = 1 to make sure that it only starts with the prefix as well, but any thoughts on this approach?

We ought not to misbehave, but we should look as though we could.

Link to comment
Share on other sites

  • Moderators

mistersquirrle,

A sensible suggestion which could have a wider application. Please try this Beta version of the UDF and see if it meets with your requirements. You will need to use a new parameter which determines if a full or partial match is required when using strings to sort the column - the function header explains in detail:

 

I am still looking into the possibility of widening the "match" options to cater for the partial string at the beginning or end of the element - however this would mean using some RegExes to compare the strings, and they always make my brain bleed!

M23

Edited by Melba23
Beta code removed

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png 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 columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...