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
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
SerSwagster
Creator
Creator

Checking multiple values in a string

Hi everyone,

I want to know if it is possible to check the presence of multiple "values" within a string.

This is my case. I have a column called COLORS filled with these records (each of them is a singular string):

green,blue

red,green

red,blue,yellow

yellow,blue,red

yellow,red,blue,green

green,red,blue

...

If I want to control that a specific color (e.g. green) is not present in the string, I do this:

if(not wildmatch(COLOR, '*green*'), 'check', 'ok')

What should I do to control multiple colors in the same string? I can't do like this ('*green,blue*') because sometimes the values are written in a different order and so the string is different.

Any suggestion?

2 Solutions

Accepted Solutions
rdelagarde
Contributor III
Contributor III

Hi,

Just like that ?

if(not wildmatch(COLOR, '*green*', '*blue*', '*red*'), 'check', 'ok')

View solution in original post

rwunderlich

I would find it a bit easier to read as 

if(
WildMatch(COLOR, '*red*') and WildMatch(COLOR, '*green*') and WildMatch(COLOR, '*blue*')
, 'check', 'ok')

-Rob

View solution in original post

13 Replies
rdelagarde
Contributor III
Contributor III

Hi,

Just like that ?

if(not wildmatch(COLOR, '*green*', '*blue*', '*red*'), 'check', 'ok')
SerSwagster
Creator
Creator
Author

Hi,

this code means that all the values are linked by an AND operator or OR operator?

tresesco
MVP
MVP

It is OR, and that probably what you need here. 

SerSwagster
Creator
Creator
Author

And in the opposite case, without NOT, that is

if(wildmatch(COLOR, '*green*'), 'check', 'ok')

How can achieve an AND solution? If i want to select all the records matching different parameters (e.g. matching green, blue and red)?

rdelagarde
Contributor III
Contributor III

Probably not the best solution but it works for an AND solution:

if(WildMatch(COLOR, '*red*'), 
  if(WildMatch(COLOR, '*green*'), 
    if(WildMatch(COLOR, '*blue*'), 'check', 'ok'),
  'ok'),
'ok')

 

 

 

 

 

 

rwunderlich

I would find it a bit easier to read as 

if(
WildMatch(COLOR, '*red*') and WildMatch(COLOR, '*green*') and WildMatch(COLOR, '*blue*')
, 'check', 'ok')

-Rob

SerSwagster
Creator
Creator
Author

OK Thanks. One last question: In the field COLOR, alongside values like yellow, green, etc., I have also more complex values like yellow-orange, green-blue etc. Records in the field COLOR are of this kind:

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:

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. How can I solve this last problem?

rwunderlich

Something along the lines of:

WildMatch(COLOR,'*yellow*','*orange*','*green*','*red*','*blue*')
and not WildMatch(COLOR,'*yellow-*'' , 'check', 'ok'))

SerSwagster
Creator
Creator
Author

Thanks for your answer, but with your solution CHECK parameter has no results,  as if it is that the two conditions (Wildmatch and not Wildmatch) are in conflict.