topic Re: Multiple IF Condition in QlikView

Multiple IF Condition

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

Hi,

I've been trying to implement multiple IF condition.

I've achieved it when I had 2 if's now I need to add 3rd if which can also be done but the problem is it's range based.

Ex:

if($(v123)>=1000000,'A',

if($(v123)<=100000,'B'))

Now I want the result if 123 is >1000000 as A

If 123 is < 100000 as B

and if 123 is in between 1 and 99999 as C

Basically it is in between a particular number

Ex:between 1 to 100 is A, between 100 to 1000 is B and greater than or equal to 10000 is C

Thanks.

stalwar1‌

vinieme12‌

Re: Multiple IF Condition

sunny_talwar — Thu, 19 Jan 2017 14:19:28 GMT

May be this:

if($(v123)>=1000000,'A',

if($(v123)<=100000,'B', 'C'))

Re: Multiple IF Condition

sunny_talwar — Thu, 19 Jan 2017 14:21:09 GMT

Or if you want to be explicit, then this

If($(v123) <= 100000, 'B',

If($(v123) < 1000000, 'C',

If($(v123) >= 1000000, 'A')))

Re: Multiple IF Condition

vinieme12 — Thu, 19 Jan 2017 14:21:42 GMT

Like below

if(123 <= 100,'A',if(123 < 1000,'B','C'))

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 14:26:43 GMT

I already tried all these conditions but doesn't seem to give me 3rd result.

Just giving A & B

I'm using this in text box

I want to show a particular number to be in B if >10000000

if <10000000 in M

And then I also want to show in K

I'm using

if($(expressionstoredinVariable)>1000000000, num($(expressionstoredinVariable)/1000000000, '$#,##0.00 B' and so.....

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 14:27:41 GMT

My current expression which is not working for K

=if($(Variable)>=1000000000 ,num($(Variable)/1000000000, '$#,##0.00 B'),

if($(Variable)<=10000000000 ,num($(Variable)/10000000, '$#,##0.00 M'),

if($(Variable)<1000000000 ,num($(Variable)/10000000, '$#,##0.00 K'))))

The problem here is it will show in M only cus we taking <10000000 as M and everything will be less after that, hence will be shown in M.

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 14:36:15 GMT

Tried but n/w

Re: Multiple IF Condition

devarasu07 — Thu, 19 Jan 2017 14:37:59 GMT

Hi,

How about like this?

=If(Sales>1 and Sales<=100,'A',if(Sales>100 and Sales<=1000,'B','C'))

Re: Multiple IF Condition

sunny_talwar — Thu, 19 Jan 2017 14:39:08 GMT

May be this

=if($(Variable) >= 1000000000,

num($(Variable)/1000000000, '$#,##0.00 B'),

if($(Variable) >= 10000000,

num($(Variable)/1000000, '$#,##0.00 M'),

num($(Variable)/1000, '$#,##0.00 K')))

Re: Multiple IF Condition

Aurelien_Martinez — Thu, 19 Jan 2017 14:41:21 GMT

=if($(Variable)<1000000000 ,

num($(Variable)/10000000, '$#,##0.00 K'),

if($(Variable)<=10000000000,

num($(Variable)/10000000, '$#,##0.00 M'),

if($(Variable)>=10000000000,

num($(Variable)/1000000000, '$#,##0.00 B')

)

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 14:41:50 GMT

Sorry to say but I've tried this as well and it gives me 0.00 M for values in thousands and for values in M it is giving 1.00 M for everything.

Re: Multiple IF Condition

sunny_talwar — Thu, 19 Jan 2017 14:43:52 GMT

Would you be able to share a sample where this isn't working?

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 14:55:24 GMT

Thanks but I've tried all the suggestions given here and sadly none is working....

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 15:01:47 GMT

Sorry I cannot.

A. This application is quite big in size.

B. The stored variable has 9 line expression in it and using 15 different dimensions.

cannot create a sample for this.

Re: Multiple IF Condition

sunny_talwar — Thu, 19 Jan 2017 15:11:43 GMT

Create a dummy app where you can replicate the issue because if you are not able to share, we won't know what might be going wrong.... because in general it should work

Re: Multiple IF Condition

vinieme12 — Thu, 19 Jan 2017 15:11:58 GMT

intead of using Num() to format have you tried formatting it as Money? and then assign Symbols for Thousand/Million/billion? on the Number tab?

Re: Multiple IF Condition

stiffi88 — Thu, 19 Jan 2017 15:12:01 GMT

=if($(Variable) >= 1 and $(Variable)<= 100, A, if($(Variable)>=101 and $(Variable) <=1000, B ,if($(Variable)>=1001 and $(Variable) <=10000, C,'NotMatched')))

Re: Multiple IF Condition

stabben23 — Thu, 19 Jan 2017 15:13:38 GMT

=if($(Variable)>=1000000000 ,num($(Variable)/1000000000, '$#,##0.00 B'),

if($(Variable)<=10000000000 ,num($(Variable)/10000000, '$#,##0.00 M'),

if($(Variable)<1000000000 ,num($(Variable)/10000000, '$#,##0.00 K'))))

here, if a value is 999999999, both M and K will be true. Qlikview Choose M

Re: Multiple IF Condition

MK9885 — Thu, 19 Jan 2017 15:17:01 GMT

Yes I agree.

Is there a function to look for in between values?

Like if 1 to 100 A

<1000 to 99 as B

and > 1000 as C

Something like this?

When I use the above expression, it gives me 0.00 M for values in thousands.

Re: Multiple IF Condition

Anonymous — Thu, 19 Jan 2017 15:22:21 GMT

arvind,

There have been a lot of good responses that should work.

If it still doesn't, I'm inclined to think the issue is tied to your data values. Can you validate the value of your variable when you run the script in debug mode?