Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

filter out records based on keywords

Hi,

I have a table where one of the fields is called Keywords

e.g Keywords:

     "good book"

     "bad book"

      "www.book.com"

      "buy great books"

     "medical Books"

     "reading medical"

       etc.

I would like to filter out all records where field Keywords doesn't contain word "book" (either lower case or upper case). So the expected result would be all the above keywords except "reading medical". What function do I use for it? How would the code look like?

I am a beginner with QV and coding so please help me to write the code for it.

Thanks

7 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

Heyy dszl136154,

You can use the below mnsion script:

Sum({<Keywords  ={"*book*"}>}  Amount)

Assuming the Amount as the Field Containing AMOUNT.

the above expression will give sum of all values where the Keywords contains the "book" .

Regards,

Nilesh Gangurde

Not applicable
Author

I think I didn't explain the problem properly. There is nothing in the data what needs to be summed.

The table has 5 fields (with 200 records):

KeywordsSourceDateTimeGroup
good bookA01/10/201212:15GOOD
bad bookA02/10/201213:15BAD
buy great booksA03/10/201214:15GOOD
medical BooksB04/10/201215:15GOOD
reading medicalB05/10/201216:15GOOD
www.book.comB06/10/201217:15GOOD

I would like the code to filter out all records where field Keywords doesn't contain word "book".

So the result would look like this:

KeywordsSourceDateTimeGroup
good bookA01/10/201212:15GOOD
bad bookA02/10/201213:15BAD
buy great booksA03/10/201214:15GOOD
medical BooksB04/10/201215:15GOOD
www.book.comB06/10/201217:15GOOD

Thank you.

johnca
Specialist
Specialist

In your script try;

If ( Keywords = '*book*' , Keywords , Null() ) as Keywords

Then in your table or chart don't display null values.

Siva_Sankar
Master II
Master II

Hi,  you can use wildmatch for this scenerio.  Use the expression as WildMatch([Keywords], '*boo*') = 1 Find the qvw file.

CELAMBARASAN
Partner - Champion
Partner - Champion

Do you want to filter it in script?

if so

below query filter records contains book

Load

*

Resident

tablename

where Wildmatch(Keywords,'*book*');

Or need to be done in expression

in expressions use set analysis as {<Keywords={"*book*"}>}

johnca
Specialist
Specialist

Thanks Siva and Celambarasan, I love learning new functions (wildmatch) that are more efficient.

Not applicable
Author

Thanks guys. I will try the solutions all of you suggested.