Jump to content
OldGuyWalking

Filtering results in arrays

Recommended Posts

OldGuyWalking

Given an array with multiple columns that is displayed in a listview,

 ===> What is the fastest/most efficient way to create and manage multiple filters and display results in ListView.

I have a text file that loads into a listview that has string, numeric, and date columns.  The main file contains about 5100 rows. It's loaded into an array and (in this ListView) it's pre-filtered to display a range of rows based on a start and end date.  On the form I have menu options for various filters. (see below).

I have options to filter on an "Air Date" column (=Today, >=Today, <=Today) and on a numeric field that is either 1 or 0 that indicate Active or Ended.

TVDB.thumb.jpg.f3fdc274af83f736c4194487e1e67e8b.jpg

For each filter option I have a prebuilt array that holds a subset of the main array based on a single filter.  For the list above I have the main Array and 5 additional arrays.  None of the arrays are updated since this is for "view only" purposes.  This is a short list and I could have done the filtering "live" but I have several of these forms and so kept the same functionality in each. I have another ListView that displays the complete 5100 row list with 3 filters that, when building the filters live was considerably slower than using prebuilt arrays.

If I want to expand past simple single column filtering, using an array for each filter becomes cumbersome especially if I want to combine filters using AND & OR.

The text file I'm working with has 16 columns. If I setup filters for 4 columns and include AND / OR capability that would require prebuilding 24 arrays to cover the various combinations.

If using the slower method of building a filtered array in real time each time a different filter is selected is the only way to go with this then I'll live with it. It is less overhead. .

Below is the code I'm currently using to "filter" an array.  My next change was going to add AND / OR functionality (see the info above the header for where I was going with this) .

; Description ...:  Delete rows from an array and only keep rows that meet the crtieria of identified columns.
;
;   Next Change: Add AND/OR to combine filters. Use array to hold multiple criteria and values?
;
;   Local $aCriteria[][] = [["",$iColNbr1, $sOperator1, $vValue1], ["AND",$iColNbr2, $sOperator2, $vValue2], ["OR",$iColNbr3, $sOperator3, $vValue3]]
;   The first set of criteria ["", $iColNbr1, $sOperator1, $vValue1] must start with a "". 
;   If anything is entered in that first parameter it will be ignored.
;   If the first parameter in any additional criteria set is left blank, or it is not OR, it will default to AND.
;   If $aArray is 1 dimension with more than one set of criteria, only the first set will be used.
;   Any criteria that uses a column that is less than 0 or higher than the total number of columns in the array will return an error.
;
;   Recognized data types for this function are:  S (String), D (Date), N (Number).
;
;   Recognized Operators are: "EQ", "NEQ", "IN", "GT", "GE", "LT", "LE", "BETWEEN".
;   ****** Not all operators work with all data types.

; #FUNCTION# ====================================================================================================================
; Name ..........: _ArrayFilter
; Description ...:  Delete rows from an array and only keep rows that meet the crtieria of identified columns.
; Syntax ........: _ArrayFilter(Byref $aArray[, $iCol = 0[, $sOperator = "EQ"[, $vValue = ""[, $iOptionBase = 0]]]])
; Parameters ....: $aArray              - Array being filtered.
;                  $iCol                - [optional] Column to filter.  Default is 0.
;                  $sOperator           - [optional] Operator. Default is "EQ".
;                  $vValue              - [optional] Criteria to compare the column/row value against.
;                  $iOptionBase         - [optional] Starting row. Default is 0.
; Return values .: None
; Author ........: OldGuyWalking
; Modified ......: 
; Remarks .......: 
; Related .......: 
; Link ..........: 
; Example .......: No
; ===============================================================================================================================
Func _ArrayFilter(ByRef $aArray, $iCol = 0, $sOperator = "EQ", $vValue = "", $iOptionBase = 0)
    Local $hFunc = _ArrayFilter

    $vValue = StringStripWS($vValue, 3)

    If $vValue = "[Today]" Then
        $vValue = _NowCalcDate()
    EndIf

    Local $sMsg
    Local $sMsgHdr
    Local $n1
    Local $sDeleteIndex
    Local $aDeleteIndex
    Local $iCnt = 0
    Local $iRows
    Local $iColMax
    Local $iDim
    Local $sData
    Local $sVType
    Local $sDType
    Local $LBound
    Local $iDiff

    If $iOptionBase <> 0 Then
        $iOptionBase = 1
    EndIf

    If _IsValueEmpty($aArray) Then
        Return SetError(1, 0, "")
    EndIf

    $iDim = UBound($aArray, $UBOUND_DIMENSIONS)
    If $iDim = 1 Then
        If $iCol <> 0 Then
            $iCol = 0
        EndIf
    EndIf

    If $iDim = 2 Then
        $iColMax = UBound($aArray, $UBOUND_COLUMNS) - 1
        If $iCol > $iColMax Or $iCol < 0 Then
            Return SetError(1, 0, "")
        EndIf
    EndIf

    If Not _IsBetween($iDim, 1, 2) Then
        ;############### MSG2 - START ###############
        $sMsgHdr = FuncName($hFunc) & " :Line: " & @ScriptLineNumber & " :Error= " & @error
        $sMsg = "Invalid Dimensioned Array. Must be a 1 or 2 dimensional array."
        MsgBox(0, $sMsgHdr, $sMsg)
        Return SetError(1, 0, "")
        ;############### MSG2 - END ###############
    EndIf

    ; Identify what the value is
    ; If it is not a String, Int, Number, or Date then skip.

    Select

        Case _DateIsValid($vValue) = 1
            $sVType = "D"

        Case IsNumber($vValue) = 1
            $sVType = "N"

        Case IsString($vValue) = 1
            $sVType = "S"

        Case Else
            ;############### MSG2 - START ###############
            $sMsgHdr = FuncName($hFunc) & " :Line: " & @ScriptLineNumber & " :Error= " & @error
            $sMsg = "Comparison value must be a " & @CRLF & _
                    "1. Date in YYYY/MM/DD format " & @CRLF & _
                    "2. A string " & @CRLF & _
                    "3. A number " & @CRLF
            MsgBox(0, $sMsgHdr, $sMsg)
            Return SetError(1, 0, "")
            ;############### MSG2 - END ###############
    EndSelect

    $iCnt = 0
    For $n1 = UBound($aArray) - 1 To $iOptionBase Step -1

        If $iDim = 1 Then
            $sData = StringStripWS($aArray[$n1], 3)
        ElseIf $iDim = 2 Then
            $sData = StringStripWS($aArray[$n1][$iCol], 3)
        EndIf

        Select
            Case _DateIsValid($sData) = 1
                $sDType = "D"

            Case IsNumber($sData) = 1
                $sDType = "N"

            Case IsString($sData) = 1
                $sDType = "S"

            Case Else
                $sDType = "U"

        EndSelect

        If _IsValueEmpty($sData) Then
            $sDeleteIndex &= $n1 & ","
            $iCnt += 1
            ContinueLoop
            ; $sDType = $sVType
        EndIf

        If Not _IsValueEmpty($sData) And $sDType <> $sVType Then
            $sDeleteIndex = $sDeleteIndex & $n1 & ","
            $iCnt += 1
            ContinueLoop
        EndIf

        Select
            Case $sOperator = "EQ"
                Switch $sDType
                    Case "D"
                        $iDiff = _DateDiff("D", $vValue, $sData)
                        If $iDiff = 0 Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "S"
                        If $sData = $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "N"
                        If $sData = $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch

            Case $sOperator = "NEQ"
                Switch $sDType
                    Case "D"
                        If $sData <> $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "S"
                        If $sData <> $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "N"
                        If $sData <> $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch

            Case $sOperator = "IN"
                Switch $sDType
                    Case "S"
                        If StringInStr($sData, $vValue) Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch

            Case $sOperator = "GT"
                Switch $sDType
                    Case "N"
                        If $sData > $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "D"
                        $iDiff = _DateDiff("D", $vValue, $sData)
                        If $iDiff > 0 Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch

            Case $sOperator = "GE"
                Switch $sDType
                    Case "N"
                        If $sData >= $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "D"
                        $iDiff = _DateDiff("D", $vValue, $sData)
                        If $iDiff >= 0 Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch

            Case $sOperator = "LT"
                Switch $sDType
                    Case "N"
                        If $sData < $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "D"
                        $iDiff = _DateDiff("D", $vValue, $sData)
                        If $iDiff < 0 Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch

            Case $sOperator = "LE"
                Switch $sDType
                    Case "N"
                        If $sData <= $vValue Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop

                    Case "D"
                        $iDiff = _DateDiff("D", $vValue, $sData)
                        If $iDiff <= 0 Then
                            ContinueLoop
                        EndIf
                        $sDeleteIndex &= $n1 & ","
                        $iCnt += 1
                        ContinueLoop
                EndSwitch
        EndSelect
    Next

    If $iCnt > 0 Then
        _DeleteArrayRows($aArray, $sDeleteIndex)
    EndIf

EndFunc   ;==>_ArrayFilter

Thanks in advance.

OldGuyWalking

Share this post


Link to post
Share on other sites
czardas
Posted (edited)

Why store filtered arrays? Can you not loop through all the filter conditions for each entry and create the filtered results (array) on the fly? The more filters, the fewer results: so you can make use of keywords such as ContinueLoop to speed things up. Just my initial thoughts.

Edited by czardas

Share this post


Link to post
Share on other sites
JLogan3o13

Moved to correct forum


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
mikell
15 hours ago, OldGuyWalking said:

What is the fastest/most efficient way to create and manage multiple filters and display results in ListView.

If your file with 5100 rows is intended to grow, the best way by far is to use a SQLite database (and the SQLite.au3 UDF). It allows a huge amount of data and is extremely powerful and fast even when using multiple filters

Otherwise filter the array as czardas said. Or read the file, filter the lines, and read the new text to an array the same way _FileReadToArray does... all these ways are much slower

  • Like 1

Share this post


Link to post
Share on other sites
jchd

I warmly support the SQLite approach. After a while it'll reveal much more flexible and powerful than any other pedestrian solution, regardless of the number of entries (hundreds or gazillions).

The reason why the SQL way is much easier is that in SQL you explain precisely the result you want (almost in plain english) without ever having to consider how this result will be obtained. Out of the blue example:

I want all columns of all entries meeting the following criteria:
  series = "000330710" and episode contains the letter "d"
or
  seriesname starts with "b" and network = "NBC"
displayed in decreasing airdate order.

This could translate into:

select * from tbl where series = '000330710' and episode like '%d%'
union all
select * from tbl where name like 'b%' and network = 'NBC'
order by airdate desc;

  • Like 1

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)

Share this post


Link to post
Share on other sites
OldGuyWalking
Posted (edited)

Thanks for the responses. 

Apologies.  I'd accidently deleted some text from my original post (before I posted) about planning on porting this to SQLite in the future.

I  started this project to build an alternative to a portable freeware app I use called EpCheck that pulls  XML data from TVDB.com using their API.  The Epcheck developer doesn't use a database but loads all XML data into memory for speed purposes.  I like the program and the author did a great job on it. I've been using it as my own personal TV Guide to track specific shows since 2015.

But, the Epcheck app doesn't allow for any ad hoc filtering and limits sorting to column headers.  It also doesn't store the data in a central database which are all things I want to build into my version.  Mine will also manage any TV Show files on disk (i.e. rename based on TV Show names or user defined alias's, organize, move, delete, search for episodes on disk, attach file locations to TV Show Episodes in order to watch videos  or open explorer at the files location, etc.). 

Epcheck was written in AutoHotKey and I know my biggest hurdle (learning curve) is going to be replicating some of the GUI features, i.e. Group header in a listview, a monthly calendar that displays 4 to 6 weeks of shows with each "day" being a listview that shows the time and show (that's up to 42 listviews in a monthly calendar).

But, since I'm still building code for the app and determining what features make sense, etc., I won't be ready to port for several weeks/months and I have many functions that still need to be tested and/or built and simplified.

I am a fan of SQL and it's power. I've been using it since the 1990's after I bought a copy of a DOS database program called R:Base that had SQL built-in.  So I do understand it's power and think it will add a lot to the app. 

Edit

I think the consensus was to port to SQLite or filter live and deal with any slowness that occurs until I port.   Agree with both. However, I have never seen any algorithms or discussions on theoretical best practices on ways to manage filtering if data when dealing with text files.  If anyone ever comes across any discsusions in any obscure and likely outdated forums anywhere I'd appreciate it if you'd let me know.  I'm sure there must have been some thoughts about this before the advent of the Database. 

If anyone is interested in the _ArrayFilter function let me know. It may be a bit limited (and needs to be cleaned up a bit before it can be used "out of the box") but for smaller arrays it might be useful.  I'm using it now for my 5100 test file on a couple of listview forms and it works reasonably well.

Thanks

OldGuyWalking

Edited by OldGuyWalking
Summary-Request for ancient info

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

  • Similar Content

    • rm4453
      By rm4453
      Hello,
       
      I am currently writing a program that parses a massive table from a website, and need a way to add a progress bar while parsing.
      I am currently using the function _IETableWriteToArray($oObj, True) to parse the array. I need the progress bar to update as the table is parsed, not just at the end of the parsing.
      Any help at all would be very much appreciated!
       
      *EDIT --> The array I am left with after parsing is $array[0-50000][16]
    • TrashBoat
      By TrashBoat
      So Im trying to make a simple 2d game and make some sort of collision detection so why not to make a 2 dimensional array but i have no clue how  to write it in multiple lines
      Global $map[5,5] = [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0 _ [0,0,0,0,0] something like this but it doesn't work
    • Zein
      By Zein
      #include "..\Include\Array.au3" #include "..\Include\File.au3" #include "..\Include\AutoItConstants.au3" Local $aRetArray Local $sFilePath = "n.csv" _FileReadToArray($sFilePath, $aRetArray, ",") ; _FileReadToArray($sFilePath, $aRetArray, $FRTA_COUNT, ",") _ArrayDisplay($aRetArray, "Original", Default, 8) The above code shows two versions of _FileReadToArray and both don't work as expected.
      The first one doesn't use the comma as a delimiter. (so I get a single column array)  I tried adding "Default" between $aRetArray and "," then it told me it had an incorrect number of parameters. 

      I looked again at the documentation:
       
      #include <File.au3> _FileReadToArray ( $sFilePath, ByRef $vReturn [, $iFlags = $FRTA_COUNT [, $sDelimiter = ""]] )
      And I with or without the flags params I should be getting a 2D array due to my file being a csv. 
      I then tried a regular flag, $FRTA_COUNT, and it tells me that I'm using a variable $FRTA_COUNT while it's not declared. Tried putting in 1 instead and it told me again, incorrect number of params. 

       
    • ternal
      By ternal
      Hi,
      Recently I have had the need to do a sort and then do a second sort while the item of the first sort stays the same ( double sorting , first on column x then while column x is the same sort column y).
      I did not put much efffort into error checking but so far I did not need it.
      For my applications so far it works perfectly however if someone is willing I want to test this extensivly.
      If anyone has big lists of random stuff to sort could you try this out please?
      #include <Array.au3> ; #FUNCTION# ==================================================================================================================== ; Name ..........: _ArraySort_Double ; Description ...: ; Syntax ........: _ArraySort_Double (Byref $array[, $first_index = Default[, $second_index = Default[, $ascending = Default]]]) ; Parameters ....: $array - 2d array to sort. ; $first_index - [optional] first column to sort. Default is 0. ; $second_index - [optional] second column to sort. Default is 1. ; $ascending - [optional] ascending/descending. Default is 1. ; Return values .: 1 if no errors occured , -1 if errors occured ; Author ........: Ternal ; Remarks .......: Needs excessive testing. ; Related .......: _arraysort() ; =============================================================================================================================== Func _ArraySort_Double (byref $array, $first_index = Default, $second_index = Default, $ascending = Default) Local $temp_value Local $counter = 1 If UBound($array, $UBOUND_DIMENSIONS) <> 2 Then MsgBox(0, "error", "error") return -1 EndIf If $first_index = Default Then $first_index = 0 If $second_index = Default Then $second_index = 1 If $ascending = Default Then $ascending = 1 _ArraySort($array, $ascending, 0, 0, $first_index); you can alter settings of primary sort here If @error Then MsgBox(0, "error", @error) return -1 EndIf $temp_value = $array[0][$first_index] For $x = 1 to UBound($array, 1) - 1 If Mod( $x, 10000) = 0 Then ConsoleWrite("at " & $x & " of a total : " & UBound($array, 1) & @CRLF) If $array[$x][$first_index] = $temp_value Then $counter+= 1 If $x = UBound($array, 1) - 1 Then; do last line here(if last line is not a new item) _ArraySort($array, $ascending, $x - $counter, $x, $second_index);you can alter settings of secondary sort here(don't forget to place line 34 the exact same) If @error Then MsgBox(0, "error", @error) return -1 EndIf EndIf Else If $counter > 0 Then ;at least 2 of the same _ArraySort($array, $ascending, $x - $counter, $x - 1, $second_index);you can alter settings of secondary sort here(don't forget to place line 29 the exact same) If @error Then MsgBox(0, "error", @error) return -1 EndIf $counter = 1 EndIf EndIf $temp_value = $array[$x][$first_index] Next Return 1 EndFunc Kind regards, Ternal
    • TrashBoat
      By TrashBoat
      So I've made this script that detects how long i have held down my left mouse button for and stores the information in an array and then sorts its using _ArraySort but the output is half sorted half broken.
      Here's my script:
      HotKeySet("{F1}","_exit") #include <Misc.au3> #include <Timers.au3> #include <Array.au3> Local $dll = DllOpen("user32.dll") $on = False Global $array[0] While(1) If _IsPressed(01,$dll) Then $timer = _Timer_Init() While _IsPressed(01,$dll) Sleep(1) WEnd $time = _Timer_Diff($timer) _ArrayAdd($array,"Time: " & Floor($time) & " ms") ;~ ConsoleWrite("Time: " & Floor($time) & " ms" & @CRLF) EndIf Sleep(50) WEnd Func _exit() _ArraySort($array) _ArrayDisplay($array) Exit EndFunc And the output:

      See how its not sorted?  What is the problem here?
×