topic Compare Field and Pattern in QlikView

Compare Field and Pattern

Tue, 13 Jul 2010 14:11:04 GMT

Hello!

I get no solution for following problem:

I have 2 fields: 1 works as a pattern, e.g. *example* or *test.com. the other one "URL" contains several web addresses, e.g. http://www.test.com

I would like to generate a new field "Pattern_match" from these both fields where only the values of URL are included which match the pattern.

I already tried a solution like this, but then the matches have to be 100% identical:

Pattern_match:
LOAD fieldvalue('pattern_url',iterno()) as match
AUTOGENERATE 1
WHILE len(fieldvalue('pattern_url',iterno()))
;
INNER JOIN (Pattern_match)
LOAD fieldvalue('URL',iterno()) as match
AUTOGENERATE 1
WHILE len(fieldvalue('URL',iterno()))
;

Compare Field and Pattern

disqr_rm — Tue, 13 Jul 2010 14:32:59 GMT

Enclosed could be one option is your list of patterns are not too long and you are not working with millions of records.

Compare Field and Pattern

Tue, 13 Jul 2010 15:16:11 GMT

thank you! it works but indeed the script now takes 21 seconds to calculate up from 3. in the url list i have got about 1500 datasets

Compare Field and Pattern

rwunderlich — Wed, 14 Jul 2010 06:53:22 GMT

I like Rakesh's solution. You might be able to speed it up by eliminating the for each loop. I don't think it necessary. I think it can be done with:

P4:
load URL as MatchedPattern
RESIDENT P2
WHERE wildmatch(URL, $(vAllPaterns));

See attached modifcation of Rakesh's example.

-Rob

Compare Field and Pattern

Wed, 14 Jul 2010 07:47:22 GMT

after testing, rakesh's solution is the faster one for my case and works just fine. thanks to the both of you!