topic Re: Match 2 comma separated lists in a resource efficient way in App Development

Match 2 comma separated lists in a resource efficient way

fredrik_olsson — Thu, 15 Sep 2022 13:29:15 GMT

Hi,

I'm trying to find a resource efficient solution to see if any value in the first comma separated list matches any value in the second comma separated list (case insensitive).

list1 = 'TOMMY,MARTIN,CHARLIE,VICTORIA';
list2 = 'FRED,VICTOR,VICTORIA,ELLEN,JOE';

if (wildMatch(list1, list2), 'matches', 'no matches')

Any help is appreciated.

Re: Match 2 comma separated lists in a resource efficient way

Or — Thu, 15 Sep 2022 13:40:43 GMT

Probably the easiest way to do this would be to subfield() the first list (in script) and then wildmatch the subfielded list with the second list. Whether or not this would be resource efficient depends on both the number of rows this needs to be applied to and the typical length of the lists.

Re: Match 2 comma separated lists in a resource efficient way

fredrik_olsson — Thu, 15 Sep 2022 13:48:02 GMT

It's applied to approx 500.000 rows in the load script.
Let's say the data after some preparation looks something like this:

OrderID, List1, List2
123123, 'TOMMY,MARTIN,CHARLIE,VICTORIA', 'FRED,VICTOR,VICTORIA,ELLEN,JOE'

Is there a faster / more efficient way of doing it?

Re: Match 2 comma separated lists in a resource efficient way

marcus_sommer — Thu, 15 Sep 2022 14:01:28 GMT

Did you try the suggestion from @Or ? Regarding to your provided information such subfield-load would create 2 M of records and a wildmatch against it should also not take very long. If it's too long what would be your expectation?

- Marcus

Re: Match 2 comma separated lists in a resource efficient way

fredrik_olsson — Fri, 16 Sep 2022 08:35:36 GMT

Thanks @marcus_sommer & @Or for trying to help out.

I tried with subfield but I can't get a match for same values in different locations in the different lists:

=if (WILDMATCH('A1,B2', SubField('C3,A1', ',')), 'Match', 'No match')

I can't get A1 to match in the example above. Any help is appreciated.

Re: Match 2 comma separated lists in a resource efficient way

marcus_sommer — Fri, 16 Sep 2022 09:05:20 GMT

You may try it in this way:

load *, rowno() as RowNo, wildmatch(List1, '*' & ListPart & '*') as Flag;
load *, subfield(List2,',') as ListPart, recno() as RecNo;
load * inline [
OrderID, List1, List2
123123, 'TOMMY,MARTIN,CHARLIE,VICTORIA', 'FRED,VICTOR,VICTORIA,ELLEN,JOE'
];

- Marcus

Re: Match 2 comma separated lists in a resource efficient way

MarcoWedel — Mon, 19 Sep 2022 21:15:52 GMT

another solution might be

If(Evaluate('WildMatch('','&List1&','&Chr(39)&',''*,'&Replace(List2,',',',*'',''*,')&',*'')'),'matches', 'no matches') as Match

though not quite sure about the performance of the string operations and evaluation in comparison to the subfield approach.

Re: Match 2 comma separated lists in a resource efficient way

MarcoWedel — Tue, 20 Sep 2022 17:06:56 GMT

slightly shorter

If(Evaluate('WildMatch('','&List1&','',''*,'&Replace(List2,',',',*'',''*,')&',*'')'),'matches', 'no matches') as Match