How to FILTER duplicates in Slices

AppSheet AppSheet Q&A
1

SELECT() has a duplicate flag whereas FILTER() does not.

How to FILTER a slice to not include rows with duplicates in a column?

2

Hi @PatriceBKK

SELECT() and FILTER() can be used similarly under this form:

SELECT(tableName[key-column], condition, FALSE)

FILTER("tableName",condition)

Please note FILTER() only renders a list of references, i.e. a list of key-values.

Hence, using the key-column can not produce duplicate, as key-values are unique by definition. Then, these three expression are strictly identical in result:

SELECT(tableName[key-column], condition, FALSE)
SELECT(tableName[key-column], condition, TRUE)
SELECT(tableName[key-column], condition)
FILTER("tableName",condition)

Filtering a slice means you have a row-condition. This means it’s complex to track duplicates. You may want to share more information about your case?

1 Like
3

FILTER was not a good example.
Objective: A table “itinerary” where each row is an event. Some events are in a city, but there may be more than one event in one city. I need a view listing all the cities without duplicates, so I am making a slice of “Itinerary” to include rows with a city (thus ISNOTBLANK), but I only want each city included only once. For a Slice, the row filter is yes/no, not a list, so I cannot use SELECT() which has the [distinct-only?] flag that I need!

The expression ISNOTBLANK([City]) … but with distinct-only. (UNIQUE does not work for that purpose)

4

Would a grouping view by city do the job for your case?

If this is not, I would recommend using a “mirror” table where every row with no duplicate would be copied. And then, use this one. Not neat, but this is a workaround!

5

Maybe try something like this

‘City Slice’ Filter condition:

[Key] = MAX(SELECT(Itinerary[Key],{[City]=[_THISROW].[City]))

Explanation: This goes row by row for each record and gets the list of keys for all itinerary records with matching cities, out of that list it returns the alphabetical last key.

Now that we have a single itinerary key for each city we can filter out the keys that don’t match the key returned in the MAX(SELECT()).

Notice: Since this expression is using a select that queries once per record, make sure it doesn’t tremendously slow down your sync speed, this should be the case unless you have a query of thousands of records being computed once per thousand of records.

6

Thank you for this idea, however:
[key being [ID] in this table,
MAX(SELECT(Itinerary[ID],([City]=[_THISROW].[City])))
Returns:
The inputs for function ‘MAX’ should be a list of numeric values
[City] is a Ref type to the “Cities” table.
And, Slice filter expects a yes/no result, to include this row or not in the Slice.

7

How about?

Slice Condition:

[ID] = INDEX(SORT(SELECT(Itinerary[ID],{[City]=[_THISROW].[City])),1)

  • Didn’t know your schema regarding your Key’s Column Name, so used Key as a clear placeholder.
  • The point of the "[ID] = " included in the beginning of the original expression is to meet the need of it being a Y/S
8

Thank you again.
While the formula works, the output produces a filtered slice with random row values.
Rather than pursuing this path, we will use the “Cities” table directly with a virtual column reverse reference to the “Itinerary” table. A half-baked solution, but it will serve the immediate purpose.
Thank you again for your help.

2 Likes