topic Re: Problem with the If Condition in QlikView

Problem with the If Condition

Anonymous — Tue, 25 Jul 2017 11:56:23 GMT

Hi Geniuses,

I have two big if conditions below, I want to merge those two conditions into 1 if condition so as to put it into the expression

=if(([Actual]>[Budget]) and (ACCOUNT='PL924100T' OR ACCOUNT='PL924000T' OR ACCOUNT='PL923100T' OR ACCOUNT='PL411810' OR ACCOUNT='PL411350T' OR ACCOUNT='PL411150T' OR ACCOUNT='PL518000T' OR ACCOUNT='PL923200T'

OR ACCOUNT='PL923000T' OR ACCOUNT='PL411200T' OR ACCOUNT='PL922100T' OR ACCOUNT='PL922000T' OR ACCOUNT='PL411900T' OR ACCOUNT='PL430000T' OR ACCOUNT='PL411990' OR ACCOUNT='PL921000T' OR ACCOUNT='PL920000T'

OR ACCOUNT='PL900000T' OR ACCOUNT='PL990000T'),

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))

)

if(([Actual]>[Budget]) and (ACCOUNT='PL511000T' OR ACCOUNT='PL519000T' OR ACCOUNT='PL515000T' OR ACCOUNT='PL923300T' OR ACCOUNT='PL512000T' OR ACCOUNT='PL922200T' OR ACCOUNT='PL922300T' OR ACCOUNT='PL922400T'

OR ACCOUNT='PL516000T' OR ACCOUNT='PL517000T'

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))

)

Thanks in advance

Re: Problem with the If Condition

Anonymous — Tue, 25 Jul 2017 12:06:43 GMT

When I says merge I mean with the help of nested if

Re: Problem with the If Condition

zhadrakas — Tue, 25 Jul 2017 12:18:23 GMT

i would recommend you to create a variable in script:

LET vAccountsArray = 'PL511000T', 'PL922400T'; .... (all your accounts)

Then build your IF Statement like this:

if(([Actual]>[Budget]) and match(ACCOUNT, '$(vAccountsArray)',

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100))),

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100)))

Regards

tim

Re: Problem with the If Condition

Anonymous — Tue, 25 Jul 2017 12:37:20 GMT

creating the variable will only reduce the size of the if but still there are lot of conditions which I need to accomodate in nested if

this is where I got stuck bro.

Re: Problem with the If Condition

effinty2112 — Tue, 25 Jul 2017 12:40:20 GMT

Hi Shashank,

This expression combines the two and will be easier to maintain

if([Actual]>[Budget]) and

Match(ACCOUNT,

'PL924100T','PL924000T','PL923100T','PL411810','PL411350T','PL411150T','PL518000T','PL923200T',

'PL923000T','PL411200T','PL922100T','PL922000T','PL411900T','PL430000T','PL411990','PL921000T',

'PL920000T','PL900000T','PL990000T','PL511000T','PL519000T','PL515000T','PL923300T','PL512000T',

'PL922200T','PL922300T','PL922400T','PL516000T','PL517000T'),

if(fabs(([Var]/[Budget]) * 100)>100, '>100%' ,fabs(([Var]/[Budget]) * 100))

if(-(fabs(([Var]/[Budget]) * 100))<-100,'<(100%)',-(fabs(([Var]/[Budget]) * 100)))

)

I'm curious about the last part of your expression.

if(-(fabs(([Var]/[Budget]) * 100))<-100, ...

is the same as

if((fabs(([Var]/[Budget]) * 100))>100, ... which appears earlier in your expression

(multiplying an inequality by a negative number reverses the sign, '<' becomes '>')

We can simplify further to

if(fabs(([Var]/[Budget]))>1, ...

If Budget is >0 then

if(fabs(Var)>Budget, ...

Hope this helps.

Andrew