topic Re: Not Wildmatch() is not filtering the data! in QlikView

Not Wildmatch() is not filtering the data!

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am reading data from a qvd and try to filter out the data by using Where Not WildMatch statement like this:

Where NOT WildMatch(Type,'*W0*','*W1*','*W2*','*W3*','*W4*','*W5*','*W6*','*WA*','*WL*','*LLU*','*LLX*','*P0*','*PG*','*IPA*','*IP1*','*IP2*')

Why do you think this is happening?

Re: Not Wildmatch() is not filtering the data!

swuehl — Fri, 20 Oct 2017 10:03:39 GMT

Is this the only LOAD statement in your script or are you maybe loading the Type field another time in another LOAD

(like for a key field)?

Could you post the complete statement?

Is this the only filter that fails, I mean does *W0* etc. work ok?

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 10:15:27 GMT

This is the only load statement I have as I am checking my script bit by bit:

LOAD

CustNo,

CustGroup,

Order,

Type,

Date,

Time

FROM [lib://Path\Orders.qvd]

(qvd)

Where NOT WildMatch(Type,'*W0*','*W1*','*W2*','*W3*','*W4*','*W5*','*W6*','*WA*','*WL*','*LLU*','*LLX*','*P0*','*PG*','*IPA*','*IP1*','*IP2*')

;

Exit Script;

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 10:16:11 GMT

The WildMatch function will try and find a specified string in a field and return which string it matched onto, rather than a boolean true or false.

In your example, the WildMatch will return 10, as the string '*LLU*' is in the 10th position.

There are two options, you can set it to check where it is null - the IsNull function will return a boolean:

WHERE IsNull(WildMatch(Type, '*W0*', '*W1*', '*W2*', '*W3*', '*W4*', '*W5*', '*W6*', '*WA*', '*WL*', '*LLU*', '*LLX*', '*P0*', '*PG*', '*IPA*', '*IP1*', '*IP2*'))

This should work, as the WildMatch will return NULL when it cannot match any of your strings.

Alternatively (I wouldn't recommend this one as it's evaluating NULLs and values), you can evaluate as a less than:

WHERE WildMatch(Type, '*W0*', '*W1*', '*W2*', '*W3*', '*W4*', '*W5*', '*W6*', '*WA*', '*WL*', '*LLU*', '*LLX*', '*P0*', '*PG*', '*IPA*', '*IP1*', '*IP2*')<1

In which case a no match should be returned (as it will be NULL).

Hope this helps

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 10:24:56 GMT

Good point Duncan!

I am running the reload now, once it's done will let you know the result. Thanks

Re: Not Wildmatch() is not filtering the data!

its_anandrjs — Fri, 20 Oct 2017 10:39:38 GMT

Try with this put equal 1 in the expression

Where NOT

WildMatch(Type,'*W0*','*W1*','*W2*','*W3*','*W4*','*W5*','*W6*','*WA*','*WL*','*LLU*','*LLX*','*P0*','*PG*','*IPA*','*IP1*','*IP2*') = 1

LOAD

CustNo,

CustGroup,

Order,

Type,

Date,

Time

FROM [lib://Path\Orders.qvd]

(qvd)

Where NOT WildMatch(Type,'*W0*','*W1*','*W2*','*W3*','*W4*','*W5*','*W6*','*WA*','*WL*','*LLU*','*LLX*','*P0*','*PG*','*IPA*','*IP1*','*IP2*')

= 1;

Exit Script;

Re: Not Wildmatch() is not filtering the data!

effinty2112 — Fri, 20 Oct 2017 10:46:54 GMT

Hi Laleh,

Try

Where WildMatch(Type,'*W0*','*W1*','*W2*','*W3*','*W4*','*W5*','*W6*','*WA*','*WL*','*LLU*','*LLX*','*P0*','*PG*','*IPA*','*IP1*','*IP2*') >0;

Good luck

Andrew

EDITED

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 12:44:53 GMT

Nope!

I've tried all your solutions, none seem to work! ;(

Re: Not Wildmatch() is not filtering the data!

swuehl — Fri, 20 Oct 2017 13:10:09 GMT

I can't really see why your original solution shouldn't work.

Could you post a sample QVD to look at?

It would be sufficient to show only the type values, so if you don't want to post confidential data, you can store the type field like this by adding the code below your initial LOAD statement:

Export:

LOAD DISTINCT

Type

RESIDENT YourOrdersTable;

STORE Export into [lib://Path\Type.qvd];

Re: Not Wildmatch() is not filtering the data!

pradosh_thakur — Fri, 20 Oct 2017 13:11:45 GMT

LOAD

CustNo,

CustGroup,

Order,

Type,

Date,

Time

FROM [lib://Path\Orders.qvd]

(qvd)

Where WildMatch(Type,'*W0*','*W1*','*W2*','*W3*','*W4*','*W5*','*W6*','*WA*','*WL*','*LLU*','*LLX*','*P0*','*PG*','*IPA*','*IP1*','*IP2*') =0

;

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 13:42:20 GMT

Thanks pradosh,

Unfortunately, this one doesn't make any changes either. When I test the expressions in the frontend, the result is as it's expected but in the load statement I don't see any improvements.

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 15:06:20 GMT

Maybe try with different variations of the same string with '.' in it? i.e. instead of '*LLU*', try:

'*.LLU.*'

'*.LLU*'

'*LLU.*'

Maybe the dot is messing up the search phrases.

Also, does it make a difference when you just use one of the values?

Try

Where NOT WildMatch(Type,'*LLU*')

If that works, it could be a problem with the combination in the expression

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 15:36:23 GMT

Just tested this one! no luck!!

Do you know ant other ways of filtering out those values considering the fact that I'm doing a resident load

and don't have access to the database.

Re: Not Wildmatch() is not filtering the data!

swuehl — Fri, 20 Oct 2017 15:47:18 GMT

You are doing a resident LOAD? You were telling us that you are loading from a QVD.

If you do a resident LOAD, how does your script look like? Maybe you are performing auto-concatenation, and what you see are the original table values. That would explain why you think your filter does not work.

Re: Not Wildmatch() is not filtering the data!

Anonymous — Fri, 20 Oct 2017 15:56:55 GMT

The next thing I'd try is to include the evaluation as a field in the table:

TempTable:

LOAD

CustNo,

CustGroup,

Order,

Type,

Date,

Time,

WildMatch(Type, '*W0*', '*W1*', '*W2*', '*W3*', '*W4*', '*W5*', '*W6*', '*WA*', '*WL*', '*LLU*', '*LLX*', '*P0*', '*PG*', '*IPA*', '*IP1*', '*IP2*') as Test,

If(WildMatch(Type, '*W0*', '*W1*', '*W2*', '*W3*', '*W4*', '*W5*', '*W6*', '*WA*', '*WL*', '*LLU*', '*LLX*', '*P0*', '*PG*', '*IPA*', '*IP1*', '*IP2*')>0,1,0) as Test2

FROM [lib://Path\Orders.qvd]

(qvd);

Even at this point, you'll be able to see the table how QV will see it - so will be quite useful if you could attach a screenshot of the table with the Type, Test, and Test2 fields in it.

And then do a resident load with a filter based on 'Test' or 'Test2':

NoConcatenate

Table:

LOAD *

Resident TempTable

WHERE Test <1;// or WHERE Test2 = 0

DROP TABLE TempTable;