Trying to select list not in the list of another list

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

Please enlighten.

@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)

Oops, thanks @Steve

1 Like

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 :smile:
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.

@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?