Not Wildmatch() is not filtering the data!

[Anonymous]

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?

[swuehl]

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?

[Anonymous]

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;

[Anonymous]

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

[Anonymous]

Good point Duncan!

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

[its_anandrjs]

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;

[effinty2112]

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

[Anonymous]

Nope!

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

[swuehl]

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];

[pradosh_thakur]

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

;