Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I have a string column COLOR, filled with records like these.
green,orange,yellow
red,blue,purple
blue,red,purple
blue,red
etc.
There are two possible occurrences (despite the order in which colors are written):
green,orange,yellow
red,blue,purple
I want to check when a record does not have the desired combination, for example:
blue,red (missing purple)
yellow,green (missing orange)
I thought a script like this, but it does not work:
if(Wildmatch(COLOR, '*blue*','*red*','*purple*'), (if(NOT Wildmatch (COLOR, '*blue*','*red*','*purple*'), 'Check', 'Ok')),
(if(Wildmatch(COLOR, '*yellow*','*green*','*orange*'), if(NOT Wildmatch (COLOR, '*yellow*','*green*','*orange*'), 'Check', 'Ok')), 'Ok')
I used two nested wildmatch for each case to check if all the values are present simultaneously in the string. Where am I wrong?
I think solving it just with match() which queries all ok-combinations might be easier, like:
match(COLOR, 'green,orange,yellow', 'orange,green,yellow', 'yellow,orange,green', 'orange,yellow,green', ...)
It shouldn't be too many combinations to use such an approach.
Beside this you may also split the multiple colors with a subfield() load-loop and concat() them again to get an ordering of the colors. Also thinkable is to create all combinations within a mapping-table.
An alternatively might be to replace the color-information with (byte-stuffed or a similar logic) numbers and then to (range-)sum() them.
- Marcus
I think solving it just with match() which queries all ok-combinations might be easier, like:
match(COLOR, 'green,orange,yellow', 'orange,green,yellow', 'yellow,orange,green', 'orange,yellow,green', ...)
It shouldn't be too many combinations to use such an approach.
Beside this you may also split the multiple colors with a subfield() load-loop and concat() them again to get an ordering of the colors. Also thinkable is to create all combinations within a mapping-table.
An alternatively might be to replace the color-information with (byte-stuffed or a similar logic) numbers and then to (range-)sum() them.
- Marcus