topic Re: Using an IF statement in a variable in QlikView

Using an IF statement in a variable

Wed, 25 Nov 2020 16:16:04 GMT

I have been trying to put the below expression into a variable so I can exclude all instances where the result is within 10%. If the variable works I want to use is in set analysis in the following way; {<vVariance11%=1>} hence pulling out the records with a variance of over 10%.

vVariance11%=if(([Under Distance]/[Original contract mileage])<=-0.1 and ([Under Distance]/[Original contract mileage])>= 0.1,0,1)

When I add this variable as a dimension in a straight table it always returns '1' and doesn't take into account the condition in the IF statement. Can anyone point me in the right direction to make this work?

Re: Using an IF statement in a variable

vishsaggi — Wed, 26 Jul 2017 16:29:42 GMT

Can you share a sample with an expected output you are looking for? Would be easy to look into and work on.

Re: Using an IF statement in a variable

oscar_ortiz — Wed, 26 Jul 2017 19:59:52 GMT

Helen,

You may want to try adding that if statement to your script and creating a new [Variance Flag] column in your table.

If(

([Under Distance]/[Original contract mileage]) <= -0.1

and

([Under Distance]/[Original contract mileage]) >= 0.1,0,1

) as [Variance Flag]

Then you could easily use that in your set analysis statement. {< [Variance Flag] = {1} >}.

Good luck

Oscar

Re: Using an IF statement in a variable

Thu, 27 Jul 2017 15:16:13 GMT

Hi Oscar

Many thanks for your suggestion.

I have tried adding the IF statement to the load script as you suggest above (excluding the = in case that was causing problems), see below.

IF(([Under Distance]/[Original contract mileage])<-0.1 and ([Under Distance]/[Original contract mileage])>0.1,0,1) as [Variance Flag],

It still isn't applying the flag '0' to records falling within a 10% tolerance. E.g. I have a variance of 0.07 and the '1' flag is being applied to it, along with every other record.

I have added the following calculation into the load script:

[Under Distance]/[Original contract mileage] as [Variance],

My thinking was maybe I could use this derived field as part of the IF statement. However, while I can use it as a dimension, it does not help me capture only records with a variance of +/- 10%.

Does anyone have any other suggestions?

Re: Using an IF statement in a variable

oscar_ortiz — Thu, 27 Jul 2017 15:46:56 GMT

Helen,

I believe it's the if statement itself, try it with an OR instead of an AND.

Good luck

Oscar

Re: Using an IF statement in a variable

oscar_ortiz — Thu, 27 Jul 2017 16:05:42 GMT

I've added an example document.

Re: Using an IF statement in a variable

Thu, 27 Jul 2017 16:33:29 GMT

Many thanks Oscar. That worked perfectly.