What is happening with Intersect() function?

[HoaHi]

Today, I used Intersect() function for comparing 2 column with type "Enumlist" but it error. Before, it worked well. Do you know what happen with this function?This is table that will be filter base on Tag columnThis is Table that i using to filterThe formula in slice. It's error with Intersect( function

[dbaum]

The ANY function returns a single item, not a list. Both arguments in your INTERSECT function need to be a list.

If you indeed need the second argument to be only the first item from the Filter[Tag] column, put it in a list:

LIST(ANY(Filter[Tag]))

[HoaHi]

You can see both of column [Tag] in 2 table is "enumlist" type. Because table Filter only has 1 row. So i use any(..) to pick this row.

[SkrOYC]

Is "Filter" a table where each user can filter?

You want a Slice of the "Notebook" table where the items inside [Tag] are common to the ones on the [Tag] field of the "Filters" table?

Please try to explain things on a understandable way

[HoaHi]

I want to find in [Tag] on table Filter at least one item that also in the column [Tag] in table Notebook. I intend use formula:

COUNT(INTERSECT([Tag],any(Filter[Tag])))>0 to Slice for table Notebook.

[dbaum]

The ANY function returns a single arbitrary item from a list--it does not compare whether "any" from a list is present in another list.

Try just plain INTERSECT:

ISNOTBLANK(INTERSECT([Tag],Filter[Tag]))

 If that doesn't accomplish what you need, there may be nuances to work through regarding Filter[Tag] being a list of lists. It might need to be first split and then recombined into a list.

[HoaHi]

I know your mean. But i want you know cause [Tag] is "enumlist" type so [Tag] in a row (not entire column)  also a list. I just want to know why intersect() not receive "enumlist" for arguments. I remember it worked well before.

Ps: i try to use 

IN([Tag],any(Filter[Tag]))

Appsheet accept above formula. But it not my expected result. I dont understand why [Tag] is a enumlist but appsheet isnt error.

[dbaum]

Thanks for explaining. I see your point now. ANY should indeed return a list when the item it returns is itself an enumlist value. If it worked previously and now does not work, I suggest report to AppSheet Support.

Your IN expression only checks for [Tag] within a single row's value from the FIlter[Tag] column--not the entire Filter[Tag] column across all rows. Instead of using ANY in this IN expression, you could try splitting and then recombining the values from the Filter[Tag] column. I've not used nor even tested that technique, but it might be as simple as:

LIST(SPLIT(Filter[Tag], ","))

[HoaHi]

Thanks for your answer. I will try your idea.

[Suvrutt_Gurjar · Accepted solution]

Hi @dbaum ,

You mentioned : " ANY should indeed return a list when the item it returns is itself an enumlist value."

As per my understanding, an expression wrapped with ANY() makes whatever inside expression into a single element value ( text, date, etc. depending on what type the list is comprised of) , even if the column type may be enumlist. So wrapping any expression in an enulist or list column with ANY() for final result will give an error. We may request @Steve for his guidance for his insight into this topic.

@HoaHi 

For the slice filter expression you may want to give a try to 

COUNT(INTERSECT([Tag], SPLIT(ANY(Filter[Tag])," , ")))>0

Edit: Minor change to suggested expression.

[HoaHi]

COUNT(INTERSECT([Tag], SPLIT(ANY(Filter[Tag])," , ")))>0

Thanks for your formula. It worked. That mean any(filter[Tag]) return a text  -

equivalent: CONCATNATE([Tag]) at first row of table. Right?

[dbaum]

It indeed seems like that's the actual behavior. Nonetheless, at least conceptually and in some actual cases, ANY of a list of lists does return a list, including for text values. Potentially, behavior differs between client and server processing of this scenario.

[Suvrutt_Gurjar]

Interesting observation @dbaum . I had a list of individual elements ( date , text etc.)  in mind while describing ANY() behavior that it returns a single element thereby ceasing to be list.

Your observations on ANY() returning a LIST() with list of LIST()s is interesting. 

From these examples, it could be deduced  that ANY() reduces a list of elements into a single element and list of lists into a single list- a kind of one level stepdown.

Thank you very much for sharing these interesting implementations of ANY() with list of lists.

[Steve]

Always treat the value given by ANY() as a singular value, not a list. There may be contexts where it returns a list, but those are exceptions.

[Suvrutt_Gurjar]

@HoaHi ,

You wrote  

That mean any(filter[Tag]) return a text  -

equivalent: CONCATNATE([Tag]) at first row of table. Right?

I believe, this is correct if [Tag] is a single element column such as date, text etc. If [Tag] is enumlist , then it is again not correct. Also by definition ANY() returns "any arbitrary element of the list, even though, yes, it practically returns the first element of the list.

[Koichi_Tsuji]

@Suvrutt_Gurjar  is right in this case with his expression.

I assume @HoaHi is on Free account for now, so this expression is working for a slice condition. However, it is not working on paid lisenses for now.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Slice-view-rendering-wrong-set-of-rows/td-p/438...

Hope a fix will be released soon by AppSheet devs.

Until then, the same expression can not be used in slice condition, but should be used in VC in sliced table (VC) and VC will be used for a slice.  Troublesome indeed......

[SkrOYC]

I recommend to not rely on List of List because I remember we talked with someone, maybe @Phil?, about it and it seemed to be a bug that you can also find on INDEX() if I recall.

Anyway, on those scenarios I mix everything into one list by using list addition and/or concatenate/split if it fits my need