topic Re: Filter rows based on words in description in QlikView

Filter rows based on words in description

Fri, 09 Sep 2011 13:43:53 GMT

Hello,

I'm loading an Excel document with some "search terms". I'm also loading a table with a description column. I want to only load the rows of this table where the description contains one of the search terms loaded from the Excel file.

I can't really use exists() here because the fields aren't identical (the search terms from the Excel file are moslty merely one word while the description is often a sentence).

Filter rows based on words in description

IAMDV — Fri, 09 Sep 2011 14:29:23 GMT

Have you tried using wild card search? Can you provide the sample files, I can try this one for you.

In QV you can use :

* Zero or more characters.

? Any single character.

Cheers - DV

Re: Filter rows based on words in description

Fri, 09 Sep 2011 15:13:18 GMT

Hi,

if u don't have too much record u can try this.

It's the first stupid idea i had.

After last load in the script, u can delete my new dimension to reserve memmory space.

Let me know.

Re: Filter rows based on words in description

Fri, 16 Sep 2011 10:25:50 GMT

Good idea, but I have way too many records. I solved it by making the selection outside of QlikView, then.

Re: Filter rows based on words in description

Fri, 16 Sep 2011 11:32:21 GMT

You could do something like this. If the excel file contains a lot of records there are likely better ways to do it

seach_terms:
load Seach_term from xxx.xls;
let condition_string='index(description,'& peek('Seach_term',0,'seach_terms')&')>0';
for counter=1 to noofrows('description')-1
     let condition_string='$(condition_string)'&' or index(description,'& peek('Seach_term',$(counter),'seach_terms') &')>0'
next counter
data:
load xxx,description from xxx where $(condition_string);