Filtering results in arrays

[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.

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) .

expandcollapse popup

; 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

[czardas]

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 May 14, 2018 by czardas

[JLogan3o13]

Moved to correct forum

[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

[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;

[OldGuyWalking]

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 May 15, 2018 by OldGuyWalking
Summary-Request for ancient info