Matching two comma separated lists

[fredrik_olsson]

Hi,

I'm trying to match 2 comma separated lists in wildmatch.
I want the "if statement" to return true if any orderID match from one list matches any in the second list.

list1 = '234,323,342,345,567,453';
list2 = '645,517,234,711';

if (wildmatch('*' & list1 & '*', '*' & list2 & '*') > 0, 'MATCH FOUND')

Any help is appreciated. 

[Or]

This isn't how WildMatch works. What you're trying to do would match the entire list, not individual components.

Consider using SubField() in your script to break one of the lists down into individual values and then you can use WildMatch (or use Subfield on both and then you can match directly or using Mapping Load). You could also loop through each value and look it up, though this would be inefficient.

[fredrik_olsson]

Thanks @Or  for trying to help, very appreciated. 
How do I in the best way look for matches after doing SubField?

if (wildmatch(SubField(list1), '*' & list2 & '*') > 0, 'MATCH FOUND')

[Or · Accepted solution]

I'm a bit confused as to what your expected outcome is - since you are only inputting one line, but this line might have multiple matching values, are you expecting the result to be multiple lines (one per ID)?

If that's the case, you would use e.g.

Load list1_id, if(wildmatch(list2,'*' & list1_id & '*'),'MATCH FOUND') as Has_Match;

Load Subfield(list1,',') as list1_id, list2

From SomeTable;

Have a look at the documentation for the Subfield script function (which is not the same as the Subfield chart/expression function) for more information on how to use it.

[fredrik_olsson]

Thanks again @Or .

I just want to know if any ID in the first list matches any ID in the second list. I don't need to know which one or how many matches, just if there is a match or not. 

Looks like this works:
if (wildmatch(SubField(list1), '*' & SubField(list2, ',') & '*') > 0, 'MATCH FOUND')

[Or]

In that case, then yes, the Subfield() version you used should work. Glad you got it worked out!