Confict between Wildmatch

[SerSwagster]

Hi everyone,

I have the field COLORS (string) filled with values like these:

green,blue

red,green-blue

red,blue,yellow-orange

yellow,blue,red

yellow-orange,red,blue,green

green,red,blue

I made this specific code to count the records that contain a specific color (NAME represent another column):

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and WildMatch(COLOR,'*yellow*','*orange*','*green*','*red*','*blue*'), 'check', 'ok'))

The problem is that in the row with name 2022, I want to select only occurrences with yellow, orange, etc. values, not also yellow-orange! But wildmatch sees the yellow word and takes also records with yellow-orange value.

I tried also to implement a double WildMatch for Name '2022_' to avoid this, but it neither works, as the two conditions lead to a conflict:

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and (WildMatch(COLOR,'*yellow*','*orange*','*green*','*red*','*blue*') and NOT WildMatch(COLOR,'yellow-*','*green-*', 'check', 'ok'))

Any suggestion?

[marcus_sommer]

You may use the wildcard-signs of * and ? by specifying the searches but you mustn't. This means the following should work:

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and WildMatch(COLOR,'yellow','orange','*green*','*red*','*blue*'), 'check', 'ok'))

- Marcus

[SerSwagster]

Hi Marcus, the problem is that each value is a string like this:

{yellow, red, green}

{orange, blue}

{yellow-orange, green}

{yellow, orange}

So I can't use wildmatch without the sign * because yellow is not the only word in the string. I have to find a way to use wildmatch that recognize yellow but not yellow-orange e.g.

[marcus_sommer]

Ok. But then it should be possible to reverse to the previous logic and to use the value-delimiter within the search, like:

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and WildMatch(COLOR,'*yellow,*','*orange,*','*green*','*red*','*blue*'), 'check', 'ok'))

- Marcus

[SerSwagster]

This solution could be right, unless the substring is in the last position of the string. Here an example:

{green-blue, yellow}

In this case, yellow is not recognized because being in the last spot means that it is not followed by ','. In all the remaining cases, it works.

[marcus_sommer]

This case could be also added to the search-items, like:

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and WildMatch(COLOR,'*yellow,*','*yellow}*','*orange,*','*green*','*red*','*blue*'), 'check', 'ok'))

- Marcus

[SerSwagster]

Sorry Marcus, I misspoke. Every value has no curly bracket. I put it only to be more readable on this discussion, but the values are like:

yellow, red, green

orange, blue

yellow-orange, green

yellow, orange

I'm sorry for the misunderstanding.

[marcus_sommer · Accepted solution]

In this case should then the following work:

if(Left(NAME, 5) = '2019_' and WildMatch(COLOR,'*yellow-orange*','*green-blue*), 'check',
if(Left(NAME, 5) = '2022_' and WildMatch(COLOR,'*yellow,*','*yellow','*orange,*','*green*','*red*','*blue*'), 'check', 'ok'))

If there are really more (complex) combinations of search-items (not only related to your colours else to all kinds of string-concats) which aren't always unique because of overlapping values you may avoid such troubles by replacing the string-values through numbers. There are multiple ways possible, for example just with indexing them starting with 1 to n - directly or maybe also within a dual(). Instead of this index you may also use one of the color-codes methods. Although it looked like more efforts it could make things easier and saving in the end more time as using such logics.

- Marcus