Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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;
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
Good point Duncan!
I am running the reload now, once it's done will let you know the result. Thanks
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;
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
Nope!
I've tried all your solutions, none seem to work! ;(
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];
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
;