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

    • lattey
      By lattey
      hi,
      i have checkboxes and each checkbox that checked, i put in array. 
      now, im stuck on how to loop the checked array and store in in one variable. what i can do now, is only write the result into a text file. 
      below is the code:
      #include <GUIConstantsEx.au3> ;~ #include <MsgBoxConstants.au3> #include <ButtonConstants.au3> #include <Array.au3> Global $Count = 3 Global $CheckBoxP[$Count] Global $step[$Count] global $array1[1] Global $ExitResult $hGUI = GUICreate("Summary Steps", 500, 400) GUISetFont(12, 400, "Tahoma") GUICtrlCreateLabel( "Please Select the Summary Steps for Script Check", 70, 20) GUISetFont(10, 400, "Tahoma") Global $array_Pstep[3] = ["fix2","fix1","fix3"] global $step[3] = ["2","3","4"] $Spacing = 50 For $i = 0 To UBound($array_Pstep) - 1 $CheckBoxP[$i] = GUICtrlCreateCheckbox($array_Pstep[$i], 80, $Spacing + (20 * $i), 65, 17) Next $submit = GUICtrlCreateButton("Submit",180, 280, 80, 30) $exit = GUICtrlCreateButton("Exit",180, 320, 80, 30) GUISetState() While 1 $Msg = GUIGetMsg() Select case $Msg=$submit For $i = 0 To $Count - 1 If GUICtrlRead($CheckBoxP[$i]) = $GUI_CHECKED Then _ArrayAdd($array1, $step[$i]) EndIf Next Global $logfilerray = @WorkingDir & "\checkedlist.txt" FileDelete ($logfilerray) Global $readlogfile = FileOpen($logfilerray,1) for $a = 1 to UBound($array1) - 1 ;~ $var=$array1[$a] FileWriteLine($readlogfile,$array1[$a]) Next FileClose($readlogfile) Exit case $Msg=$exit $ExitResult = MsgBox(1,"Summary Step", "Continue to Exit ?") if $ExitResult = 1 Then ;ok Exit EndIf Exit EndSelect WEnd  
    • omicron
      By omicron
      How do you perform a nested loop function with a multidimensional array from 2 lists.
      for i in list1
      (open file) extract variable
          while open for i in list 2
          (open file2) extract variable
       
      var1 + var2 = (search term)

      The list sizes will more than likely consist of different lengths.
       
      What is the best approach to accomplishing this method?
             
    • omicron
      By omicron
      Hello!

      I am working on a function that I am just getting lost on. The goal is a multiple nested loop.

      Here are the steps:
      Contents of file1.txt::
      [topic] var1=Name var2=OtherName var3=SomeotheName Contents of file2.txt::
      [subTopic] top=sub1 top2=sub2 top3=sub3 The Shell I am working from::
      #include <file.au3> $file = "c:\yourfile.txt" FileOpen($file, 0) For $i = 1 to _FileCountLines($file) $line = FileReadLine($file, $i) msgbox(0,'','the line ' & $i & ' is ' & $line) Next FileClose($file) Understanding however that the "msgbox" needs to then become a variable. in example the following::
      $file = "c:\yourfile.txt" FileOpen($file, 0) While true( prog.exe is running && "WinName" is open) do For $i = 1 to _FileCountLines($file) $line = FileReadLine($file, $i) ;Open File to log "current location of file 1" FileWriteLine ("filename", $i & ' is ' & $line) var = $line Next $file2 = "c:\yourfile.txt" FileOpen($file, 0) For $i = 1 to _FileCountLines($file) $line = FileReadLine($file, $i) ; OpenFile to log "Current location of file 2" FileWriteLine ("filename", $i & ' is ' & $line) Next FileClose($file2) FileClose($file) The goal in written form is the following ::

      While in "OpenWindow"
          read from file 1 starting at line 1 until end of file.
         file 1 is a list of names to be searched.
         With $line selected, add this element to the element in file 2.
       
      The search of a variables in list 1 and list 2 differ on the amount of posts that day. (This is not a web based platform, it is a game) I need to search 2 names and take a screenshot of the out put. The sizes of the names list depend on the activity of names at the time of search.
      This loop continues until all the names from both lists have been searched. Mostly in the format of::
      File1= item
      File2= Vendor
       
      Item + Vendor  ( Capture screen, scroll) -- Not sure how to detect if I need to scroll)
       
      Thank you for your help and support!
    • Skeletor
      By Skeletor
      Hi Virtual People,
      My array works perfectly fine. However, what is the best practice if the line in the array doesn't have the correct amount of columns and if I can add a placeholder?

       
      For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") Next  
    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
×