topic Re: Excluding range of values in a script statement in QlikView

Excluding range of values in a script statement

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

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.

Re: Excluding range of values in a script statement

sunny_talwar — Mon, 01 May 2017 18:29:34 GMT

Are these numeric Values? May be this

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

Re: Excluding range of values in a script statement

swuehl — Mon, 01 May 2017 18:31:02 GMT

You can use numeric comparison operators:

Load *

From [Filename]

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

Re: Excluding range of values in a script statement

CarlosAMonroy — Mon, 01 May 2017 18:42:23 GMT

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

Re: Excluding range of values in a script statement

jchambers123 — Tue, 02 May 2017 12:38:38 GMT

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.

Re: Excluding range of values in a script statement

jose_vargas — Fri, 23 Mar 2018 14:53:34 GMT

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

Re: Excluding range of values in a script statement

swuehl — Fri, 23 Mar 2018 19:57:37 GMT

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

You probably want an OR logic.