Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: filter out records based on keywords

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

Re: filter out records based on keywords

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.

johncaqc
Valued Contributor

Re: filter out records based on keywords

In your script try;

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

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

Siva_Sankar
Honored Contributor

Re: filter out records based on keywords

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

Re: filter out records based on keywords

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*"}>}

johncaqc
Valued Contributor

Re: filter out records based on keywords

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

Not applicable

Re: filter out records based on keywords

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

Community Browser