Why does the IN() expression return N whereby, there are similar values in our Ref Table?
Please enlighten.
Solved! Go to 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)
@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
Oops, thanks @Steve
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?
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |