topic Re: Dynamic mask expression for wildmatch in QlikView

Dynamic mask expression for wildmatch

teempi — Wed, 21 Sep 2016 09:26:50 GMT

Hey,

Is it possible to give Wildmatch() a dynamic mask expressions based on fields in the data? I've attached an example application that illustrates what I'm trying to do. I'd like the Result/Result2 fields to be TRUE if any of the values in "Mask" field matches with the value in the "Dim" field. So in my example, rows 1,2,4 and 5 should be TRUE.

It seems to me that if the second parameter for Wildmatch is based on a field value (not a static string or a variable), QlikView compares the first parameter to the WHOLE mask, not the individual parts. I'm sure this can be solved by using loops and/or variables in some way but I'd like to avoid those in this case.

edit: if the data contains values "AAA", "AAAA" etc. it should NOT match with "A". "A*", "AA*" etc. should match though.

edit2: by using SubField() it's possible to extract individual values to get the correct result. Is there a way to do it without SubField though since it does generate (potentially) a lot more rows?

-Teemu

Re: Dynamic mask expression for wildmatch

tresB — Wed, 21 Sep 2016 09:33:40 GMT

Try like:

SubStringCount(MaskModified,Dim) as Result3,

Re: Dynamic mask expression for wildmatch

Gysbert_Wassenaar — Wed, 21 Sep 2016 09:34:26 GMT

You can use the index function: index('ABC', 'B')

Re: Dynamic mask expression for wildmatch

teempi — Wed, 21 Sep 2016 09:43:29 GMT

Thanks for your reply! Unfortunately row 1 returns FALSE if you use SubStringCount. Also if the data contains Dim value "AAA", that should NOT match with mask "A" but should match with "A*".

-Teemu

Re: Dynamic mask expression for wildmatch

teempi — Wed, 21 Sep 2016 09:44:44 GMT

Thanks for your reply! I believe this solution has the same problem as tresesco's. If the data happens to contain dim "AAA", mask "A" incorrectly matches that value. Only "A*" etc. should match "AAAA".

-Teemu

Re: Dynamic mask expression for wildmatch

Gysbert_Wassenaar — Wed, 21 Sep 2016 10:10:59 GMT

I don't see how 'AAA' can ever match 'ABC' or even 'A;A;A'.

Re: Dynamic mask expression for wildmatch

teempi — Wed, 21 Sep 2016 10:18:50 GMT

Index('AAA', 'A') returns 1. So does Index('AAA', 'AA') etc. I would like that only something similar to 'A*" would return 1 (or TRUE). In your example, I would like 'B' NOT to match with 'ABC'. Basically I need to be able to use wildcards.

If you think Index should work, could you provide a quick example? Thanks!

-Teemu

Re: Dynamic mask expression for wildmatch

Gysbert_Wassenaar — Wed, 21 Sep 2016 10:39:41 GMT

Guess I just don't understand what you're trying to do.

Have a look at what this does:

LOAD Dim, Mask, min(Result) as Result Group By Dim, Mask;

LOAD *, if(WildMatch(Dim,MatchValue), -1, 0) as Result;

LOAD Dim, Mask, Subfield(Mask,';') as MatchValue INLINE [

Dim, Mask

AAA, A;B;C

AAA, A;A;A

A, ABC

A, A;B;C

A, *

B, A

B, B

B, A;B;C

B, ABC

B, B*

B, *B*

AB, *B*

AAA, ?AA

ABA, ?A*;B;C

ABA, ?A?;A?C;A?A

];

Re: Dynamic mask expression for wildmatch

teempi — Wed, 21 Sep 2016 11:09:38 GMT

This solution does seem to work and it's actually the one I'm currently using myself.

While this does work fine, I'm wondering if there's a way to do it without extracting the individual field values via SubField(). I believe SubField creates a new row for each individual value and this might cause some issues if there's a lot of data (the example I provided is a lot simpler than the actual scenario I need this for).

-Teemu