Solved: Re: compare two lists

bolognesiedalla · 03-22-2023 10:47 AM

How can I compare two lists?

I would like to create a yes/no result expression based on the values in list B contained in list A

example:
list A (1 , 2 , 3, 4)

if
list B (1, 2)
result yes

if
list B (1, 5)
result no

in practice, if list B contains even a single value not present in list A, it must be "no". If all the values of list B are contained in A, it must be "yes".

What's the easiest way to do this? maybe it's a trivial thing for many but...

Marc_Dillon

COUNT( ListB ) = COUNT( INTERSECT( ListB , ListA ) )

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

View solution in original post

Marc_Dillon

COUNT( ListB ) = COUNT( INTERSECT( ListB , ListA ) )

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

bolognesiedalla

Hi, I set the recommended formula, I use it in a dashboard where a "control box" slice filters the view.

dashboard view

I have this problem: if in the filter "Utente Corrente[Filter_Obiettivi]" (LIST B) I insert all the values of "[TARGETS]" (LIST A) correctly shows a result, while if in "Current User[Filter_Objectives]" I insert only 1 o 2 values present in "[OBIETTIVI]" disappears instead of remaining. If TEST formula is done, the incredible is that the result is YES (see photo) so you should see a line.

ALL THE ITEM IN [OBIETTIVI]

RESUL TEST

ONLY 2 ITEM OF [OBIETTIVI], INSTEAD 3

If, on the other hand, I select values not included in LIST A, it rightly excludes it.

The formula used works like this: (mechanism inspired by @MultiTech - hope he can help me too)

if the filter is not empty, use this criterion, otherwise do not filter - repeated several times.

AND(

[CLIENTE]="901989bd",

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_coltura],1)),IN(INDEX(Utente Corrente[Filtermodelli_coltura],1),[COLTURA]),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_tipo_impianto],1)),IN([TIPO_IMPIANTO],Utente Corrente[Filtermodelli_tipo_impianto]),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_ferti_sino],1)),
[FERTI_SINO]=INDEX(Utente Corrente[Filtermodelli_ferti_sino],1),true),

if(
and(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_ferti_n],1)),
INDEX(Utente Corrente[Filtermodelli_ferti_sino],1)="yes"),

ifs(INDEX(Utente Corrente[Filtermodelli_ferti_n],1)="1 > 3",[N_FERTI_DOCCIA]<=3,
INDEX(Utente Corrente[Filtermodelli_ferti_n],1)="4 > 6",
and([N_FERTI_DOCCIA]>3,[N_FERTI_DOCCIA]<=6),
INDEX(Utente Corrente[Filtermodelli_ferti_n],1)="7 > 9",
and([N_FERTI_DOCCIA]>6,[N_FERTI_DOCCIA]<=9),
INDEX(Utente Corrente[Filtermodelli_ferti_n],1)=TEXT("10 +"),[N_FERTI_DOCCIA]>=10),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_fog_sino],1)),
[FOG_SI_NO]=INDEX(Utente Corrente[Filtermodelli_fog_sino],1),true),

if(
and(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_fog_n],1)),
INDEX(Utente Corrente[Filtermodelli_fog_sino],1)="yes"),
ifs(INDEX(Utente Corrente[Filtermodelli_fog_n],1)="1 > 3",[NFOGLIARI]<=3,
INDEX(Utente Corrente[Filtermodelli_fog_n],1)="4 > 6",
and([NFOGLIARI]>3,[NFOGLIARI]<=6),
INDEX(Utente Corrente[Filtermodelli_fog_n],1)="7 > 9",
and([NFOGLIARI]>6,[NFOGLIARI]<=9),
INDEX(Utente Corrente[Filtermodelli_fog_n],1)="10 > 12",
and([NFOGLIARI]>9,[NFOGLIARI]<=12),
INDEX(Utente Corrente[Filtermodelli_fog_n],1)=TEXT("10 +"),[NFOGLIARI]>=13),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filter_Obiettivi],1)),
COUNT(SPLIT(Utente Corrente[Filter_Obiettivi]," | "))=
COUNT(INTERSECT(SPLIT(Utente Corrente[Filter_Obiettivi]," | "),SPLIT([OBIETTIVI]," | "))),true)

)

the above parts of the formula all work. I used the SPLIT function in the final piece because INTERSECT reported this error: it is not possible to include a list of a list.

What am I doing wrong? could it be a bug?

bolognesiedalla

in practice with the second part you extract the elements in common to the two lists. Then verify that list B has the same number as the list that came out of INTERSECT. Right?

and it should work whatever number of elements the two lists have.

Seems like a great approach to me. Thank you

MultiTech

The general approach I've settled on is a combination of IsNotBlank() & Intersect()

IsNotBlank(Intersect(
  [List_Column], 
  Split(Concatenate(Current_User[User_List_Filter]), " , ")
))

For your situation @bolognesiedalla you might try something like the following:

if(ISNOTBLANK(INDEX(Utente Corrente[Filter_Obiettivi],1)),
IsNotBlank(Intersect(
  SPLIT([OBIETTIVI]," | "),
  SPLIT(Concatenate(Utente Corrente[Filter_Obiettivi])," | ")
)),
true
)

This is assuming that [OBIETTIVI] is not already a list; if it is you can remove the SPLIT() from that part of the formula.
I've included a Concatenate() around the filter call (this helps ensure the results are "flat" and easier to work with)

If you put a formula like this into a slice, as part of a larger set of additional criteria inside an AND() statement, only the records that share a value in the [Whatever] list with the User_Filter[List] will be included in the slice.

bolognesiedalla

Wow thank you for your help. Great as always!!

[OBIETTIVI] is a field ENUMLIST of type base ref to the table "TARGETS"

Utente Corrente[Filter_Obiettivi] it is always a field ENUMLIST of type base ref to the "TARGETS" table (created in the USER table)

Your logic basically assumes that you are only showing me the rows that share a common value between the two lists.
Instead I would like that:
1. if Utente Corrente[Filter_Obiettivi] contains an element not present in [OBIETTIVI] the row is not shown.
2. Conversely if Utente Corrente[Filter_Obiettivi] contains only elements present in [OBIETTIVI] (even not all), the record is shown.

I hope I explained myself well and understood your logic correctly. Thanks again

bolognesiedalla

AND(

[CLIENTE]="901989bd",

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_coltura],1)),IN(INDEX(Utente Corrente[Filtermodelli_coltura],1),[COLTURA]),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_tipo_impianto],1)),IN([TIPO_IMPIANTO],Utente Corrente[Filtermodelli_tipo_impianto]),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_ferti_sino],1)),
[FERTI_SINO]=INDEX(Utente Corrente[Filtermodelli_ferti_sino],1),true),

if(
and(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_ferti_n],1)),
INDEX(Utente Corrente[Filtermodelli_ferti_sino],1)="yes"),
ifs(INDEX(Utente Corrente[Filtermodelli_ferti_n],1)="1 > 3",[N_FERTI_DOCCIA]<=3,
INDEX(Utente Corrente[Filtermodelli_ferti_n],1)="4 > 6",
and([N_FERTI_DOCCIA]>3,[N_FERTI_DOCCIA]<=6),
INDEX(Utente Corrente[Filtermodelli_ferti_n],1)="7 > 9",
and([N_FERTI_DOCCIA]>6,[N_FERTI_DOCCIA]<=9),
INDEX(Utente Corrente[Filtermodelli_ferti_n],1)=TEXT("10 +"),[N_FERTI_DOCCIA]>=10),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_fog_sino],1)),
[FOG_SI_NO]=INDEX(Utente Corrente[Filtermodelli_fog_sino],1),true),

if(
and(ISNOTBLANK(INDEX(Utente Corrente[Filtermodelli_fog_n],1)),
INDEX(Utente Corrente[Filtermodelli_fog_sino],1)="yes"),
ifs(INDEX(Utente Corrente[Filtermodelli_fog_n],1)="1 > 3",[NFOGLIARI]<=3,
INDEX(Utente Corrente[Filtermodelli_fog_n],1)="4 > 6",
and([NFOGLIARI]>3,[NFOGLIARI]<=6),
INDEX(Utente Corrente[Filtermodelli_fog_n],1)="7 > 9",
and([NFOGLIARI]>6,[NFOGLIARI]<=9),
INDEX(Utente Corrente[Filtermodelli_fog_n],1)="10 > 12",
and([NFOGLIARI]>9,[NFOGLIARI]<=12),
INDEX(Utente Corrente[Filtermodelli_fog_n],1)=TEXT("10 +"),[NFOGLIARI]>=13),true),

if(ISNOTBLANK(INDEX(Utente Corrente[Filter_Obiettivi],1)),
IFS(ISBLANK(SPLIT(Utente Corrente[Filter_Obiettivi]," | ")-INTERSECT(SPLIT(Utente Corrente[Filter_Obiettivi]," | "),SPLIT([OBIETTIVI]," | "))),true,
ISNOTBLANK(SPLIT(Utente Corrente[Filter_Obiettivi]," | ")-INTERSECT(SPLIT(Utente Corrente[Filter_Obiettivi]," | "),SPLIT([OBIETTIVI]," | "))),false),true)

)

I tried to modify last IF like this and it still doesn't work well:
1. WORKS WHEN - "Utente Corrente[Filter_Obiettivi]" contains all or none of [OBIETTIVI]'s elements
2. IT DOESN'T WORK WHEN for example out of three elements of [OBIETTIVI] I put only 2 in "Utente Corrente[Filter_Obiettivi]"

How can I improve it?

I tried to insert CONCATENATE but it still doesn't work. I thought the problem depended on the use of " | " as a separator for this I inserted SPLIT in each list. (I'm using it to better view the records)

bolognesiedalla

hi Matt @MultiTech

This strange thing keeps happening to me: the formula I entered to filter the rows in a slice should show only row 9 (Test result is yes), but in reality it doesn't show anything. Am I doing something wrong? If so, what is the procedure for contacting someone on Google staff directly to report this behavior?

I'm referring to this last bit, which:

I tried to modify last IF like this and it still doesn't work well:
1. WORKS WHEN - "Utente Corrente[Filter_Obiettivi]" contains all or none of [OBIETTIVI]'s elements
2. IT DOESN'T WORK WHEN for example out of three elements of [OBIETTIVI] I put only 1 or 2 in "Utente Corrente[Filter_Obiettivi]"

if(ISNOTBLANK(INDEX(Utente Corrente[Filter_Obiettivi],1)),
IFS(ISBLANK(SPLIT(Utente Corrente[Filter_Obiettivi]," | ")-INTERSECT(SPLIT(Utente Corrente[Filter_Obiettivi]," | "),SPLIT([OBIETTIVI]," | "))),true,
ISNOTBLANK(SPLIT(Utente Corrente[Filter_Obiettivi]," | ")-INTERSECT(SPLIT(Utente Corrente[Filter_Obiettivi]," | "),SPLIT([OBIETTIVI]," | "))),false),true)

dashboard (control box+slice)

Test formula slice

any idea?

MultiTech

@bolognesiedalla wrote:

Your logic basically assumes that you are only showing me the rows that share a common value between the two lists.
Instead I would like that:
1. if Utente Corrente[Filter_Obiettivi] contains an element not present in [OBIETTIVI] the row is not shown.
2. Conversely if Utente Corrente[Filter_Obiettivi] contains only elements present in [OBIETTIVI] (even not all), the record is shown.

I hope I explained myself well and understood your logic correctly. Thanks again

Ah I see, that's my misunderstanding; in that case @Marc_Dillon has the solution.

if(ISNOTBLANK(INDEX(Utente Corrente[Filter_Obiettivi],1)),
  Count([OBIETTIVI])
  =
  Count(Intersect(
    [OBIETTIVI], 
    Split(INDEX(Utente Corrente[Filter_Obiettivi],1)), " , ")
  )),
true)

--------------------------------------------------------------------------------------------------------------

I feel like there's something missing, some nuance that's not accounted for yet.

bolognesiedalla

Thanks a lot Matt for your help,

In the first chunk I have to insert the count of the control box list right?...always with SPLIT function?

if(ISNOTBLANK(INDEX(Utente Corrente[Filter_Obiettivi],1)),
  Count(Split(INDEX(Utente Corrente[Filter_Obiettivi],1), " | "))
  =
  Count(Intersect(
    [OBIETTIVI], 
    Split(INDEX(Utente Corrente[Filter_Obiettivi],1), " | ")
  )),
true)

there's something i can't get to work....it doesn't work when i add more than one record in Utente Corrente[Filter_Obiettivi] (record present in [OBIETTIVI] in the row that i use as test)

The formula makes sense but I can't figure out if the problem is:

- the separator I use in enumlists " | "

- or something that doesn't make intersect elements count well

How can I do to understand it?