Excluding range of values in a script statement

[jchambers123]

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.

[sunny_talwar]

Are these numeric Values? May be this

Where Not Match(faclnbr, '12001') and (prindiag < 617 or prindiag > 679.99);

[swuehl · Accepted solution]

You can use numeric comparison operators:

Load *

From [Filename]

Where Not Match (faclnbr, '12001') And Not (prindiag >=617 and prindiag <= 679.99);

[CarlosAMonroy]

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

[jchambers123]

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.

[jose_vargas]

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

[swuehl]

Using AND logic to combine the two ranges can't work.

You probably want an OR logic.