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

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

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

    • VollachR
      By VollachR
      Hi,
      I'm looking for a way to take a number value from a Row2 of a 2D array and according to this check if files that appear in rows 3-11 in the array exists.
      For example, if the number in Row2 is 5 I need to check for the files in Row 3-6 only, if it is 6 than rows 3-7 and so on.
      I thought on using a FOR loop but I have very little experience with those.
      Can you suggest the best way to do what I need?
      BTW, the files in Rows 3-11 will usually have blank value for any row above the number in Row2 (e.g. Row2 = 5 so Rows3-6 will have values but 8-11 be empty), The values I need are in Column 1 of the array, the name of the key from the INI file that the array was created from is in Column 0.
      Full Example:
      Row2 of Array:
      Col0 = Games# - Col1 = 5
      Rows3-6
      Col0 = Exe2 - Col1 = Path To File
      Col0 = Exe3 - Col1 = Path To File
      Col0 = Exe4 - Col1 = Path To File
      Col0 = Exe5 - Col1 = Path To File
      I need that if Row2 is 5 to check these above for rows if the file exists, if it was 6 then the next row as well and so on up until number 10 in Row2 as it can't go above 10.
      So basically for whatever number in Row2 from 2-10 need to check 1-9 rows from 3-11 to see if the files in Col1 exists and if any of them don't exist it should call a function that shows an error message.
      I'm pretty sure I have the first line of the for look correct:
      For $i = 1 To $aAIO[2][1] Just not sure how to continue from there, also not sure if $i should be equal 1 or 2.
      Help will be appreciated.
    • FMS
      By FMS
      Hello,
      I'm trying to get data from twitter to an array and so far I found an Twitter UDF whish lookes very intresting but couldn't get it to work.
      It lookes not supported any more(2010) and buggy when i read all te replies.
      More around this subject (autoit and twitter) i couldn't find on this forum.
      Is there sombody who know's a good way to get live data from twitter to an array inside autoit?
      (I kinda doubt that this isn't tackled before)
      In the end I was hoping to get all tweets from date to date from an specific subject inside a 2D array to work whit.
    • AndreasNWWWWW
      By AndreasNWWWWW
      I got a question:  i am trying to run different functions based upon what i select in these radio buttons.(code below)
      it needs to check server 1. then run function 1 or function 2 after what i selected in the checkbox.
      once that function is done it moves to the next one, until it has been trough all 5 
       
      iv'e tried using while loops with different while $i equals to something but then i manualy need to go in and edit the script every time.
      #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #Region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 615, 437, 192, 124) $Server2 = GUICtrlCreateLabel("Server2", 216, 95, 41, 17) $server1 = GUICtrlCreateLabel("Server1", 216, 72, 41, 17) $server4 = GUICtrlCreateLabel("Server4", 216, 144, 41, 17) $server3 = GUICtrlCreateLabel("Server3", 216, 119, 41, 17) $server5 = GUICtrlCreateLabel("Server5", 216, 170, 41, 17) $Start = GUICtrlCreateButton("Start", 240, 248, 147, 25) $Checkbox1 = GUICtrlCreateCheckbox("function1", 288, 72, 97, 17) $Checkbox2 = GUICtrlCreateCheckbox("function2", 392, 72, 97, 17) $Checkbox3 = GUICtrlCreateCheckbox("function1", 288, 96, 97, 17) $Checkbox4 = GUICtrlCreateCheckbox("function2", 392, 96, 97, 17) $Checkbox5 = GUICtrlCreateCheckbox("function1", 288, 120, 97, 17) $Checkbox6 = GUICtrlCreateCheckbox("function2", 392, 120, 97, 17) $Checkbox7 = GUICtrlCreateCheckbox("function1", 288, 144, 97, 17) $Checkbox8 = GUICtrlCreateCheckbox("function2", 392, 144, 97, 17) $Checkbox9 = GUICtrlCreateCheckbox("function1", 288, 170, 97, 17) $Checkbox10 = GUICtrlCreateCheckbox("function2", 392, 170, 97, 17) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit EndSwitch WEnd  
    • 31290
      By 31290
      Hi everyone, 
      I'm currently writing a script that allow me to list all currently installed software on a computer but some of the are listed in the HKLM64 hive of the registry whereas 95% of all others are in the HKLM "normal" one.
      Thing is, I'd like to combine these two reg key into one single ListView item.
      Here's my code so far, knowing that it's working on both cases (changing to HKLM64 or HKLM short)
      Thanks in advance for the help
      -31290-
    • Iceburg
      By Iceburg
      Hi everyone, I'm at best a noobie.  I have read through the Array helps, and specifically the 2D array help file, and I'm struggling to get my code working.
      I have an array that is read from a file, thats working great.  I'm trying to do some math on the array, so I can find the largest, average, lowest, day over day change %, etc.
      The array read working fine, I get 43 lines, line 0 is 44, and then I get data that looks like
      0519 $10,000
      0520 $10,001
      0521 $10,002
      The data in this array is a single 1D array, breaking it out into 2 columns so I can do the math is what I can get to happen.  
      How do I reference the array to store this data?  Second, how do I assign this data to the appropriate row/column?
      Thanks in advance.
      Dim $all_cash_amounts[UBound($aInput)][2] Dim $max_amount_in_account Dim $min_amount_in_account _FileReadToArray($LC_Check_file_path, $aInput) _ArrayDisplay($aInput) local $date = StringRegExp($aInput[1], "(\d\d\d\d)", 1) local $cash = StringRegExp($aInput[1], "\d+\s(-?[0-9\.\,]+)", 1) ConsoleWrite("Date is: " & $date & @CRLF) For $i = 1 To UBound($aInput)-1     $date = StringRegExp($aInput[$i], "(\d\d\d\d)", 1)     $all_cash_amounts[$i][2] = $date[$i][0], $cash[$i][1]      Next _ArrayDisplay($all_cash_amounts)  
×