Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
SerSwagster
Creator
Creator

Checking a group of a values simultaneously in a string

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?

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

1 Reply
marcus_sommer
MVP & Luminary
MVP & Luminary

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