Checking a group of a values simultaneously in a string

[SerSwagster]

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?

[marcus_sommer · Accepted solution]

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