Trying to select list not in the list of another list

WilsonWee
Participant I

Why does the IN() expression return N whereby, there are similar values in our Ref Table?

Please enlighten.

Solved Solved
0 10 1,789
1 ACCEPTED SOLUTION

If there are any duplicates within list1, @tonyโ€™s expression may erroneously suggest a difference, since list subtraction has the side-effect of removing duplicates.

For instance, if list1 is {1, 1, 1} and list2 is {2}, {1, 1, 1} - {2} will give {1}, the COUNT() of which is less than COUNT({1, 1, 1}) but does not reflect the occurrence of an item in list2.

To avoid:

COUNT(list1 - LIST()) > COUNT(list1 - list2)

View solution in original post

10 REPLIES 10

tony1
Participant V

@WilsonWee I suspect that youโ€™re doing something like IN(list1, list2). But thatโ€™s not how IN works: it expects the first argument to be a single element, not a list.

If you want to check whether two lists have any common elements, you can do this:

COUNT(list1) > COUNT(list1 - list2)

We probably should add a INTERSECTION(list1, list2) function, but right now we donโ€™t have it.

If there are any duplicates within list1, @tonyโ€™s expression may erroneously suggest a difference, since list subtraction has the side-effect of removing duplicates.

For instance, if list1 is {1, 1, 1} and list2 is {2}, {1, 1, 1} - {2} will give {1}, the COUNT() of which is less than COUNT({1, 1, 1}) but does not reflect the occurrence of an item in list2.

To avoid:

COUNT(list1 - LIST()) > COUNT(list1 - list2)

As I see we do have now the INTERSECT() function:
https://help.appsheet.com/en/articles/3483429-intersect

tony1
Participant V

Oops, thanks @Steve

WilsonWee
Participant I

Thanks @tony & @Steve for your input.
If I may ask further, what can I do if I want the difference values between a ref table and a enumlist?
I tried this
Select(Process[OPID], NOT(IN( Process[OPID], Select (Route[ProcessIds], [RouteID] =[_THISROW].[RouteID] ))))

Your expression does not reflect the advice we gave above. Try our suggestion.

Hi @Steve, this is my attempt as far as your suggestion shared.
Count(Process[OPID]-LIST()) > COUNT(Process[OPID]-[ProcessIDS])

So far so good.
The results is a Yes/No type. And I saw the List(values) that I am looking for shown in green box. This is the results of โ€œdifferencesโ€ from the ref List. How would you advise me to get that list as the end result ?

Using the List(List1 - List2) work?

If what you want is a list of items in Process[OPID] excluding the items in [ProcessIDS], all you need is:

(Process[OPID] - [ProcessIDS])

I suspect we misunderstood your original question, so all that about COUNT() and - LIST() probably doesnโ€™t apply.

WilsonWee
Participant I

@Steve Thanks for replying and clearing it up.

Iโ€™m still confused quite a bit on List(), I notice that using SELECT() on ENUMLIST column is considered an entity. Even if I tried to SPLIT() it , and even TEXT() to form a LIST() but it could not combine a multi selection ENUMLIST columns into a combine LIST.

In other words, Can I use SELECT() an ENUMLIST from its referencing column? OR is there a workaround on this?

Iโ€™m having difficulty understanding what you want. It seems perhaps you want a list of OPIDs that arenโ€™t included in a rowโ€™s ProcessIDS list for each row. I donโ€™t understand how EnumList, SPLIT(), TEXT(), and LIST() are involved. Could you clarify for me?

Top Labels in this Space