Look up values ​​in a field

[Anonymous]

How to search for specific text within a field that contains various information separated by commas ?

I set up a market research and multiple choice answers were built in the database by joining the Google Forms information and separating comma as the example . Need to separate the information . Thus , I thought to create three more columns that will function to search for text within a field and return it me or has not.

What your choice of colors : White , Black and Red ?	White	Black	Red
Black, Red	Not	Yes	Yes
White, Black	Yes	Yes	Not
White, Black, Red	Yes	Yes	Yes

Excel can result in using:

B2   =ISNUMBER(SEARCH("White";A2))

C2   =ISNUMBER(SEARCH("Black";A2))

D2   =ISNUMBER(SEARCH("Red";A2))

How to do this in Qlikview ?

Thanks.

Odair Jr.

Brazil

[Anonymous]

B2 For White

Pick(WildMatch(YourFieldName, '*White*) + 1, 'No', 'Yes')

C2 For Black

Pick(WildMatch(YourFieldName, '*Black*) + 1, 'No', 'Yes')

D2 For Red

Pick(WildMatch(YourFieldName, '*Red*) + 1, 'No', 'Yes')

[morgankejerhag]

A different approach - see the attached file.

if(SubStringCount(Colours,'Red'),'Yes','Not') as Red,

[Anonymous]

This approach is cleaner.

[Anonymous]

Thank you all for the answers . But I have another challenge.

And the color blue that was reported as a particular response and not listed in one answer arises?

Others need a column to manage the different colors of White , Black and Red.

What expression would the "Yes" results for Other column for Blue?

[morgankejerhag]

Here we handle unknown colors as well

purgechar(replace(replace(replace(Colours,'Red',''),'White',''),'Black',''),', ') as Other,

[Anonymous]

Perfect Morgan , solved my problem.

Thanks.