Searching one aggregate list for any values in another aggregate list

[Joooe]

I'm trying to use a table calculation to check two lists against one another, and relay whether there was a full, partial, or no match for values in Aggregate List 2 appearing in Aggregate List 1. Working Table Calculation column name is 'Match?'.

Example output:

• List 1: Red                               List 2: Red              Match?: Full Match
• List 1: Red, Blue, Green        List 2: Red, Blue     Match?: Partial Match
• List 1: Red, Green                 List 2: Red, Purple Match?: Partial Match
• List 1: Purple, Black            List 2: Blue, Red     Match?: No Match

My trouble currently is example row 3, where one item from list 2 matches, but another does not. Currently, this results in 'No Match', which I've hesitantly switched to "If more than 1 item in list, then return "Cannot Eval"".

I'd like to be able to accurately search Aggregate List 1 against all comma separated values in the Aggregate List 2, and if any are found to match, return "Partial Match".

Current Table Calculation:

if(${list_1}=${list_2},"Full Match",
if(contains(to_string(${list_1}),to_string(${list_2})),"Partial Match",(
if(${list_2_count}>1,"Cannot Eval","No Match"))))

I then attempted to replace:

to_string(${list_2})

with:

to_string(split(${list_2},","))

But only received "No Match" for the rows with the challenging condition (example row 3 above).

I think I need something more extensive to instead search each successive list value, potentially involving substring, but I am not certain.

A key factor here is that list_1 and list_2 can be any number of comma separated values long, up to 10 values, and I do not know how to accommodate this. I do have a measure for list_2_count that returns the number of values in list_2.

[Joooe]

Alright, so, I've managed to work out a solution on my own, but in all honesty I hope there is a better solution available that does not lag so much (IE, would love a more succinct solution).

I'm going to dispense with the generic example above, as re-working my solution to share in such a format would be very time consuming. The premise I'll use from here forward is that users have 'PVT Orgs' which may or may not match any of the values in their 'Log Hours' orgs, and they need to be searched one-at-a-time for Full Match/Partial Match/No Match.

First Aggregate List of Unique Values Column: Orgs of type 'Log Hours', calling 'Log Hours Orgs'. (Looks like: Org name 1, Org name 2, Org name 3. In alphabetical order)

Second Aggregate List of Unique Values Column: Orgs of type 'PVT Orgs', calling 'PVT Orgs' (Looks like: Org name 2, Org name 3, Org name 5. In alphabetical order)

Aggregate Count of unique values in 'PVT Orgs', calling 'PVT Orgs Count' (Looks like: Numeric values; currently 7 is max value)

The first Table Calc, titled '1st PVT Org', checks for: if the first org is the only org (IE if PVT Orgs Count=1), then display the PVT Orgs list (which wouldn't have a comma). Otherwise, it will look for the first comma in the PVT Orgs list, and take all text before it (-1) as a string to obtain the first list value.

if(${pvt_org_count}=1,${pvt_orgs},substring(to_string(${pvt_orgs}),0,position(to_string(${pvt_orgs}),",")-1))

The second Table Calc, '1st PVT Org with comma' is going to be referenced to shorten the overall string in the thirst table calc. First, in the 'Yes' condition, if there was only one list value, we can leave this empty "". Otherwise, return that first value with the comma (no offset number).

if(${pvt_org_count}=1,"",substring(to_string(${pvt_orgs}),0,position(to_string(${pvt_orgs}),",")))

 For the third Table Calc, 'PVT Orgs minus first', we're taking the second table calc, which was either blank or ended with a comma, and we're replacing that portion of the original list of values as a new column. This effectively removes the first comma and what came before it from the PVT Orgs List.

if(${pvt_org_count}<2,"",replace(to_string(${pvt_orgs}),to_string(${st_pvt_org_with_comma}),""))

The first three table calcs can then be repeated as many times as needed: 3 columns per list value, raising appropriate values by one each time.

2nd PVT Org

if(${pvt_org_count}=2,${pvt_orgs_minus_first},substring(to_string(${pvt_orgs_minus_first}),0,position(to_string(${pvt_orgs_minus_first}),",")-1))

2nd PVT Org with comma

if(${pvt_org_count}=2,${pvt_orgs_minus_first},substring(to_string(${pvt_orgs_minus_first}),0,position(to_string(${pvt_orgs_minus_first}),",")))

PVT Orgs minus second (from 2nd iteration forward, it needs to reference the prior iteration of itself, rather than the original PVT Orgs list)

if(${pvt_org_count}<3,"",replace(to_string(${pvt_orgs_minus_first}),to_string(${nd_pvt_org_with_comma}),""))

... and so on.

By the time I'd completed the iterations of this process up to 7 list values, I had 21 Table Calculations.

In order to then evaluate whether my individual values against the original list, I used the following final Table Calculation, titled 'Match?':

if(${log_hours_orgs}=${pvt_orgs},"Full Match",
  if(contains(to_string(${log_hours_orgs}),to_string(${st_pvt_org}))
    OR ${pvt_org_count}>1 AND contains(to_string(${log_hours_orgs}),to_string(${nd_pvt_org}))
      OR ${pvt_org_count}>2 AND  contains(to_string(${log_hours_orgs}),to_string(${rd_pvt_org}))
        OR ${pvt_org_count}>3 AND  contains(to_string(${log_hours_orgs}),to_string(${th_pvt_org}))
          OR ${pvt_org_count}>4 AND contains(to_string(${log_hours_orgs}),to_string(${fifth_pvt_org}))
            OR ${pvt_org_count}>5 AND  contains(to_string(${log_hours_orgs}),to_string(${sixth_pvt_org}))
              OR ${pvt_org_count}>6 AND  contains(to_string(${log_hours_orgs}),to_string(${seventh_pvt_org})),"Partial Match",
"No Match"))

Needless to say, when hiding each of the 21 Table Calcs that essentially exist to hold variables, I had to tell Chrome to 'wait' (not close) twice per column. The visualization does work as desired on the Dashboard, but any edits are extremely slow.