List(1 , 2 , 3 , 3 , 4) - list(1 , 2 , 3 , 4) = list() <--blank?

AppSheet AppSheet Q&A
,
1

I’m trying to find the duplicates of a list, this way I can base a special formatting rule off of it, but I’m running into the intelligence of the platform doing something I’m not wanting

Like the title says, I have the following expression results:

list(1 , 2 , 3 , 3 , 4) - list(1 , 2 , 3 , 4) = list()

  • The first list has two 3s in there (that’s the duplicate I need to isolate out, and any others).
  • The second list is generated FROM the first list via UNIQUE()
    So I guess you could test: list(1 , 2 , 3 , 3 , 4) - UNIQUE(list(1 , 2 , 3 , 3 , 4))

I’m looking for the result: list(3) - because if I take things one-for-one that’s what I would get.

But AppSheet is an intelligent platform, and before a few months ago we didn’t have the UNIQUE() function, so (after tons and tons of bugging) the devs put in some auto-magic to make lists unique when you do any list-math on them.

But we have UNIQUE() now, so this behavior is actually a hindrance.

I was hoping that LIST() might tell the system, “Hey, take this list literally; subtract one from the other and don’t do any magic.”

But no. @Adam any suggestions? Thanks

Edit: Feature Request: DUPLICATES()??? NONUNIQUE()… THEREPEATINGONES(), DOPPELGANGERS(), CLONES()

3 Likes
2

+1 for a doppelgangers expression…

1 Like
3

Whenever an operator is used, a defined standard needs to be established. In many systems, there is ALSO an analogous function provided (e.g. SUBTRACT()) which allows variations based on parameter settings. The operator is really just a shorthand syntax for the most most common function usage.

It seems thats what is needed here, a SUBTRACT() or SUBTRACTLIST() function that allows for parameter (or two) to specify to remove/keep duplicates…and any other useful feature parameters!!

A DUPLICATES() function appears to be a specialized case of SUBTRACT().

3 Likes
4

Hi @MultiTech_Visions did you find a workaround to detect duplicates in a list?

5

Theoretically;
LIST(1 , 2 , 3 , 3 , 4) - LIST(1 , 2 , 3 , 4) != LIST() as LEN(LIST[0]) = 5 where LEN(LIST[1]) = 4. However when tested the expression editor treats it like LIST(1 , 2 , 3 , 3 , 4) == UNIQUE(LIST(1 , 2 , 3 , 3 , 4)) == LIST(1 , 2 , 3 , 4) and therefore the initial equation results as LIST(). I wonder, if LIST() expression already eliminates the duplicate values in a list during eval, what’s the need for the UNIQUE() function then?

6

LeventK:

if LIST() expression already eliminates the duplicate values

It’s not LIST() doing it, it’s the list-subtract (-) operator.

LeventK:

what’s the need for the UNIQUE() function then?

Prior to UNIQUE(), subtracting an empty list was how I recommended deduplicating lists.

3 Likes
7

Steve:

It’s not LIST() doing it, it’s the list-subtract ( - ) operator.

Thanks for the tip @Steve

1 Like