Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

odair_bergamo
New Contributor II

Look up values ​​in a field

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 ?WhiteBlackRed
Black, RedNotYesYes
White, BlackYesYesNot
White, Black, RedYesYesYes

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

Tags (3)
Labels (1)
6 Replies
blackmon
New Contributor III

Re: Look up values ​​in a field

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
Contributor II

Re: Look up values ​​in a field

A different approach - see the attached file.

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

odair_bergamo
New Contributor II

Re: Look up values ​​in a field

This approach is cleaner.

odair_bergamo
New Contributor II

Re: Look up values ​​in a field

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
Contributor II

Re: Re: Look up values ​​in a field

Here we handle unknown colors as well

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

odair_bergamo
New Contributor II

Re: Look up values ​​in a field

Perfect Morgan , solved my problem.

Thanks.