Speeding up a filter for a slice

Fof_Webmaster · 08-18-2021 09:35 AM

I need to filter >12,000 records with a slice. The statement below works fine but takes a 2-3 minutes to run. This is actually a simplified statement and I need to expand it. Unfortunately I need all the logic. Would it help to run the slice filter based a separate table in GSheet that contains part or all of the filter expressions? Or would it help to divide this into 2 slices or reorder the expressions? Or are there a better expressions to use?

ISNOTBLANK(
  FILTER(
      "NRC Reporting",
      AND(
           ([_THISROW].[License Plate #] = [License Plate #]),    
           ([_THISROW].[License Plate #] <> ""),
           ([_THISROW].[License Plate State] = [License Plate State]),
       ((ABS(HOUR([_THISROW].[Timestamp] - [Timestamp])) / 24) > 14),
       ((ABS(HOUR([_THISROW].[Timestamp] - NOW())) / 24) < 31),
       (DISTANCE([_THISROW].[GPS Location], [GPS Location]) < 50)
      )
    )
      - LIST([_THISROW])
  )

Thanks
Bob

Marc_Dillon

I’ll try to help. I have a few questions first to hopefully gather some info to help with the situation.

Can you describe in plain language about what records you’re wanting in this slice? If I were to guess, from your expression, I’d say something like “show me records that aren’t too old, which have other records of the same license, and those other records aren’t too old either, and aren’t very far away.”
I’m also wondering if you perhaps should be using ISBLANK(), instead of of isNOTblank()? Obviously I don’t really know what you’re doing yet, so this is mostly a complete guess.
Just to confirm, which Table is the Slice for, the NRC Reporting Table?
Can you describe a little more about what these records are, and what your app does, and what you’re trying to find out and/or display with this Slice?

A couple possible improvements that come to mind right away:

Could you maybe add a real column to your Table, an EnumList, base type Ref, pointing to the same Table, which holds all other records with the same license info? This way, in your Slice expression you could point to that List instead of the entire Table.
Maybe create a first Slice, which only contains these 2 expressions:

…from there, create a 2nd Slice, with an expression like this:

IF(
  IN( [_THISROW] , first-slice[key-column] ) ,
  FILTER(
    ...rest of your conditions...
  )
)

This way, the FILTER() will only run if your record first meets the initial conditions, which will hopefully save some time.

Also, not sure what this is supposed to mean:

Fof_Webmaster

Thanks for the quick reply. Some clarifications in order of your questions.

Plain language: Show me the records that have matching license number and state that were made within the last 31 days, and that have timestamps more than 14 days apart, and the distance between records is less than 50 kilometers.

With ISNOTBLANK I’m just trying to skip blank entries.

Yes slice is based on the NRC Report table.

I can’t explain more about what the app is for since it is confidential.

Regarding adding a column to the table, I’m not sure I understand your point - The person making a report does not know whether the license he is entering has a match. So I’m not sure how the Enum gets entered.

I think like your #2, last idea. But I need clarification on what this does: IN( [_THISROW] , first-slice[key-column] )

Re your last question, what I mean is I could make a separate live Google Sheet that contains only the last 31 days of reports, bring that into the app and run a simpler filter off that.

Regards
Bob

Marc_Dillon

Automatically, with a FILTER() expression.

Ahh. Yah, that could be a good idea.