OldGuyWalking Posted May 14, 2018 Posted May 14, 2018 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 Posted May 14, 2018 Posted May 14, 2018 (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 May 14, 2018 by czardas operator64 ArrayWorkshop
Moderators JLogan3o13 Posted May 14, 2018 Moderators Posted May 14, 2018 Moved to correct forum "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum!
mikell Posted May 14, 2018 Posted May 14, 2018 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 czardas 1
jchd Posted May 14, 2018 Posted May 14, 2018 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; czardas 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 hereRegExp tutorial: enough to get startedPCRE 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)
OldGuyWalking Posted May 14, 2018 Author Posted May 14, 2018 (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 May 15, 2018 by OldGuyWalking Summary-Request for ancient info
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now