Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In my script I have a range of values that I want to exclude. I'm familiar with using the Match function to include or exclude certain values, but not sure how to use it for a range (or set) of values.
So for the field named "prindiag" I am trying to exclude all values between 617 and 679.99.
Load *
From [Filename]
Where Not Match (faclnbr, '12001') And Where Not Match (prindiag, ???)
I guess I'm looking for the Qlikview equivalent of a SQL "between" operator and have searched the community but haven't quite found what I'm looking for (outside of set analyses).
Can anyone please advise?
Thanks for your help.
You can use numeric comparison operators:
Load *
From [Filename]
Where Not Match (faclnbr, '12001') And Not (prindiag >=617 and prindiag <= 679.99);
Are these numeric Values? May be this
Where Not Match(faclnbr, '12001') and (prindiag < 617 or prindiag > 679.99);
You can use numeric comparison operators:
Load *
From [Filename]
Where Not Match (faclnbr, '12001') And Not (prindiag >=617 and prindiag <= 679.99);
Hi John,
You can try creating a flag for those values and excluding them using a precedent load.
Something like:
TableName:
Load *
where _flagExcludePrindiag = 0;
Load *,
if(prindiag > 617 and prindiag < 680,1,0) _flagExcludePrindiag
From [Filename]
Where Not Match (faclnbr, '12001') ;
Hope that helps,
Carlos M
Thank you all. I used the <= and >= operators, as Sunny and Stefan suggested, and it worked perfectly. I appreciate the three of you taking the time to help and advise-- much appreciated.
I am using the if statement for the "flag", it works for me when it is for a range like your example, but how do I do if I have two ranges ??? I've tried something like that but it does not work for me.
if( (prindiag > 617 and prindiag < 680) and (prindiag > 800 and prindiag < 900) ,1,0) _flagExcludePrindiag
Using AND logic to combine the two ranges can't work.
You probably want an OR logic.