Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
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
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.
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
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.
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