Match a list with a list

1minManager · 12-11-2019 03:07 AM

I want to create an expression that checks if any item in a list is in another llist. So for example:

Is LIST(A,B,C) present in LIST(A,B,C) = TRUE

Is LIST(A,B,C) present in LIST(C,D,E) = TRUE

Is LIST(A,B,C) present in LIST(D,E,F) = FALSE

Koichi_Tsuji

The number of the items in the list expression is static, i.e. always 3 or dynamic? Can you expect the number of the list?
If you have cap or max bar of number of the list items, then index expression should help.

I mean something like this? What do you think?

OR

(

index([SomeListCoumn 1],1) = index([SomeListColumn 2],1),

index([SomeListCoumn 1],1) = index([SomeListColumn 2],2),

index([SomeListCoumn 1],1) = index([SomeListColumn 2],3),

index([SomeListCoumn 1],2) = index([SomeListColumn 2],1),

index([SomeListCoumn 1],2) = index([SomeListColumn 2],2),

index([SomeListCoumn 1],2) = index([SomeListColumn 2],3),

index([SomeListCoumn 1],3) = index([SomeListColumn 2],1),

index([SomeListCoumn 1],3) = index([SomeListColumn 2],2),

index([SomeListCoumn 1],3) = index([SomeListColumn 2],3)

)

1minManager

The number of items in both lists are entirely dynamic and both are ENUMLISTs

Koichi_Tsuji

Sure, but you can not expect what would be the reasonably max number of the items? If 10 is max, then I will repeat the same method of formula for another 7 times, even max is 100.
If the number of items are ever green, then my options wont work off course.

GreenFlux

Add the two lists together and count the values (this will count duplicates, or values in both lists).

Then get the UNIQUE() values from that list and count again, then compare the counts and see if any were removed.

Koichi_Tsuji

Smart Idea!

Suvrutt_Gurjar

Yes, nice approach.

Suvrutt_Gurjar

Hi Simon,

Is the requirement necessariliy that if there is even a single item common in the two lists , the logic should return a true and that each list can be of different length? Sometimes list one number of items can be can be bigger than that in list twotwo and vice versa and soemtimes these are equal?

1minManager

Hi @Suvrutt_Gurjar

Yes the lists can be of different lengths. And Yes if one item matches that that shoudl return TRUE. If it helps, these are TEXT based lists

Koichi_Tsuji

Hi Simon, I believe @GreenFlux s idea should work. Did you test it?
I was just curious how it works.

Aleksi

One simple solution is COUNT(INTERSECT({A,B,C},{C,D,E}))>0

GreenFlux

Good call, @Aleksi. I forgot we have an INTERSECT() function in AppSheet!

Suvrutt_Gurjar

Hi @GreenFlux, Exactly, I also forgot about INTERSECT() function.

Hi @Aleksi,

This is typical Aleksi’s master stroke.

You alway utilize the most apporpriate functions to meet the logic need in the most efficient manner.

1minManager

Awesome @Aleksi that should work fine

But can someone at Appsheet add INTERSECT() to - https://help.appsheet.com/en/articles/961274-list-expressions-and-aggregates as I’d never heard of it!

Koichi_Tsuji

Every day we need to learn new stuff which is good!

I never heard intersect function. Appreciate if this is documented with use cases.

@Aleksi

Aleksi

You’re welcome

Aleksi

This function is very new… though I requested the article.

Bahbus

I came here to also suggest the Intersect function. But I only just noticed it yesterday.

Aleksi

https://help.appsheet.com/en/articles/3483429-intersect

Koichi_Tsuji

Thank you Aleksi, for sharing this.
Better to put it on FEATURE FRIDAY!
This is features having tons of impact to improve Apps in various use cases.

Thank you again.

1minManager

Presume the sytax on the help page is missing the end bracket?

INTERSECT(LIST(1, 2, 3), LIST(2, 3, 4))

Aleksi

That’s correct. Let’s fix that.

Austin_Kerr

I have two enum lists.

IF(OR((COUNT**(INTERSECT(FILTER[FILTER 2],(TARGET_MASTER[FILTER]))**)>0),(IN(ALL,FILTER[FILTER 2]))),TRUE(),FALSE())

and when I try to intersect them I get this error. they are both enum lists with base type text. what do I do!

INTERSECT: both lists must be of the same type.

Suvrutt_Gurjar

Hi @Austin_Kerr,

It sounds that your INTERSECT() expression is as below

You may wish to check if both the columns [FILTER 2] and [FILTER] are of same type so that they produce lists of same type. And both FILTER[FILTER 2] and TARGET_MASTER[FILTER] are producing lists.

Also as a side note, if I may add and request, it appears that you have table as well as column names with the word “FILTER” . Besides FILTER() is an AppSheet function as well. You may wish to rename either table or columns “FILTER” a bit differently. Say FILTER2 or SUBFILTERS or say ITEMFILTERS. This may help in faster manual evaluation of expression and possible conflict in the expression evaluation by the parser itself.

Edit: There are some other AppSheet conventions that you may wish to note. It sounds that you wish to have result of IF() as TRUE or FALSE. You may write simply TRUE or FALSE instead of TRUE() or FALSE()

Austin_Kerr

Thank you for the recommendation and noting some best practices I can keep in mind for the future.

Both columns are of the same type “enumlist” with the same base type “text”

Suvrutt_Gurjar

Thank you. And FILTER and TARGET_MASTER are two tables?

Austin_Kerr

Yes

MASTER_TARGET is the table I’m trying to slice with the formula, and [FILTER] is table I’m filtering against.

I’ve tried both MASTER_TARGET[FILTER] and [FILTER] to reference the FILTER column on the table MASTER_TARGET.

*I can see how this is confusing to write here

Suvrutt_Gurjar

Thank you. If both the columns are enumlist type, could you please add, if you are trying to compare them for a certain row in respective tables? Or are you trying to combine them across entire set of rows in respective tables and compare? In each row , are those enumlist columns having more than one value in list?

If one tries to create list of enumlists across multiple rows, typically it results into a list with many duplicate values from that enumlist column in various rows. If enumlist options are A, B, C , then their list across several rows is likely to have many multiple A, B and Cs such as {A,B, A,C, B,C] and so on.

Suvrutt_Gurjar

If you are creating list of enumlists , then better to use a an expression like below

INTERSECT(SPLIT(FILTER[FILTER 2],","),(SPLIT(TARGET_MASTER[FILTER]),","))

Stefano_Hammer

Please would you link a complete example how is possible to obtain an list of value referenced in a TAB using INTERSECT function for filtering value from others 2 TABs?

I try to explain better…
TAB_A : each row is referenced by [ID_A] and has a column [ENUMLIST] with possible values “a” and/or “b” and/or “c”
TAB_B: each row is referenced by [ID_B] and has a column [ENUMLIST] same as TAB_A with possible values “a” and/or “b” and/or “c”

TAB_C: setting ref column [“ENUMLIST ID_A”] need to filter and set column [“FILTERED ID_B”] with common values matching “a” and/or “b” and/or “c”

Thanks a lot for all your help!

Steve

What is a “TAB”? A table?

Are “TAB_A”, “TAB_B”, and “TAB_C” a tables or columns?

What does “each row is referenced by [ID_A]” mean? That the ID_A column is the key column?

What does “setting ref column [“ENUMLIST ID_A”]” mean?

Stefano_Hammer

Hi Steve, yes TAB_A , TAB_B , TAB_C are tables, [ID_A] is the key column of the rows in TAB_A.
All rows in TAB_A and TAB_B have a column [Match] of different combine of values : “a”, “b”, “c”
In the TAB_C I need to have 2 referenced column (type Enumlist), the first include all “ID_A” values, the second include a list of filtered row from TAB_B.
I would obtain only the “ID_B” rows matching the same [Match] split values of the “ID_A” row selected.
Let me know if it’s enough clear, thanks.

Steve

This is my interpretation of what you want for each row in table TAB_C:

Column ID_A references a row in table TAB_A.
Column ID_B references a row in table TAB_B.
Column ENUMLIST ID_A contains a copy of the ENUMLIST column of the row in the TAB_A table referenced by the ID_A column.
Column FILTERED ID_B contains a list of values in the ENUMLIST ID_A column that also occur in the ENUMLIST column of the row in the TAB_B table referenced by the ID_B column.

If my interpretation is correct:

The App formula expression for the ENUMLIST ID_A column of TAB_C should be:
```
[ID_A].[ENUMLIST]
```
The App formula expression for the FILTERED ID_B column of TAB_C should be:
```
INTERSECT([ENUMLIST ID_A], [ID_B].[ENUMLIST])
```

Stefano_Hammer

It’s not working, of course I wasn’t clear. Please have a look at these:

[TAB_A] is ref and is dropdown (no Valid If constrict required)
[TAB_B] is ref but require a INTERSECT expression in Valid If to filter only the value matching with any [Match] single values “a”, “b”, “c”
Ex. matching “ID_A1” match “a” with “ID_B2”, “ID_B4” and match “b” with “ID_B3” and again “ID_B4”
valid filtered result in dropdown should be: “ID_B2”, “ID_B3”, “ID_B4”

This expression help to have a right selection of matching spare parts in a catalogue app.

Thanks again for all your help.

Steve

I’m even more confused now.

Stefano_Hammer

Ouch… ok I’m sorry about it, in the meanwhile I forwarded this to a “pro” but the answer has been INTERSECT is not the right expression to use. Strange and I’m not convinct… I will wait and maybe someone else could help me to understand how do it or which alternative expression is good for resolve it.
Thank you anyway for your time.

Jos_de_Pijper

You could do this like the example below.
I was actually hoping their might be a more efficient idea in terms of amount of functions that you use, but apparently not.


ISNOTBLANK(
 INTERSECT(
  LIST(A,B,C),
  LIST(C,D,E)
 )
)