topic Re: Multiple IF statements in expression in QlikView

Multiple IF statements in expression

naveenchellaara — Sat, 23 Feb 2013 06:40:47 GMT

Can any one tell me how to use multiple 'if' statements (not nested,separate 'if' statements)in expressions?

I need have a condition where more than one possible outcomes is possible.

E.g.

HeatMap_Rules_Key	Condition	MinValue	MaxValue	Color
1	Between	0	25	Red
2	Between	25	50	Blue
3	Between	50	100	Green
4	Between	100	125	Orange

This is the lookup table and the Coloumn can have any values. I need to set the Color for the column based on the value range in this table

consider the

column1

-------------

.747

.968

.45

.50

I need to be able to do use both

IF(Condition1 = 'Between',

IF(column1<ULimit1 and column1>= LLimit1,$(ColourCode1))

)

IF(Condition1 = 'Between',

IF(column1<ULimit2 and column1>= LLimit2,$(ColourCode2))

)

The values of the variables are

ULimit1 = 25

ULimit2 = 50

LLimit1 = 0

Limit2 = 25

$(ColourCode1) = RGB(255,0,0)

$(ColourCode2) = RGB(0,255,0)

Re: Multiple IF statements in expression

phaneendra_kunc — Sat, 23 Feb 2013 07:05:04 GMT

Try this...

IF(Condition1 = 'Between',

IF(column1<ULimit1 and column1>= LLimit1,$(ColourCode1),

IF(column1<ULimit2 and column1>= LLimit2,$(ColourCode2),

IF(column1<ULimit3 and column1>= LLimit3,$(ColourCode2),

IF(column1<ULimit4 and column1>= LLimit4,$(ColourCode2)

)))))

Re: Multiple IF statements in expression

naveenchellaara — Sat, 23 Feb 2013 07:11:01 GMT

Thanks a lot,

I tried this. but i am am getting only Red Colour. I need the colours to be based on Range.

When

IF(Condition1 = 'Between',

IF(column1<ULimit1 and column1>= LLimit1,$(ColourCode1),

is satisfied, it does not go to the else part. I am only getting red colour and not the green colour.

I want both to be present.

Re: Multiple IF statements in expression

phaneendra_kunc — Sat, 23 Feb 2013 07:29:23 GMT

See attached...

Re: Multiple IF statements in expression

naveenchellaara — Sat, 23 Feb 2013 07:41:10 GMT

Thanks a lot

This is what i want.

Re: Multiple IF statements in expression

Anonymous — Tue, 06 Dec 2016 10:59:03 GMT

can u please paste tha code here i am unable to open your qvw file

Re: Multiple IF statements in expression

jjbom1990 — Fri, 09 Dec 2016 13:31:35 GMT

IF(Condition1 = 'Between',

IF(column(1)<25 and column(1)>= 0,red(),

IF(column(1)<50 and column(1)>= 25,Green(),

IF(column(1)<75 and column(1)>= 50,Blue(),

IF(column(1)<100 and column(1)>= 75,rgb(255,0,0)

)))))

Re: Multiple IF statements in expression

Tue, 05 Sep 2017 14:05:03 GMT

Hello team,

I'm new in the Qlik experience and I'm struggling with a simple formula.

Whenever I apply my formula I only have the first two ones. Anyone can help?

iF(Fabs((((Sum([Actuals Total $$]))-(Sum([YTD Budget Total $$]))))/(Sum([YTD Budget Total $$])))<=0.1,green(),

iF(Fabs(((Sum([Actuals Total $$])-(Sum([YTD Budget Total $$]))))/(Sum([YTD Budget Total $$])))>0.1 ,yellow(),

iF(Fabs(((Sum([Actuals Total $$])-(Sum([YTD Budget Total $$]))))/(Sum([YTD Budget Total $$])))>0.25,red())))

Thank you

Mohammed

Re: Multiple IF statements in expression

Demlet — Wed, 20 Dec 2017 16:50:27 GMT

Hi Mohammed,

The problem is that one of your first two conditions will ALWAYS be met. If a value is >.25 it is also >0.1. The IF statement never gets to the >.25 because the previous IF condition is met.

the second IF statement needs to account for being between 0.1 and 0.25.

Re: Multiple IF statements in expression

Anonymous — Sat, 05 May 2018 03:54:47 GMT

Hi Mohammed,

You need to modify the second statement as below:

=iF(Fabs((((Sum(Amount1))-(Sum(Amount2))))/(Sum(Amount2)))<=0.1,green(),

iF(Fabs(((Sum(Amount1)-(Sum(Amount2))))/(Sum(Amount2)))>0.1 and Fabs(((Sum(Amount1)-(Sum(Amount2))))/(Sum(Amount2)))<0.25,yellow(),

iF(Fabs(((Sum(Amount1)-(Sum(Amount2))))/(Sum(Amount2)))>=0.25,red())))

Re: Multiple IF statements in expression

Anonymous — Wed, 16 May 2018 11:46:44 GMT

I have a special need.

I do not want nested if statement.

I want to create a filter:

I have some values like :

AAA

AAA,BBB

BBB,CCC,AAA

in the expression of the filter,

I want to use :

if(WildMatch([Field Name],'*AAA*'),AAA)

if(WildMatch([Field Name],'*BBB*'),BBB)

if(WildMatch([Field Name],'*CCC*'),CCC)

then if I click AAA, I got 3, if I click BBB, then I got 2, if I click CCC then I got 1

so how should I write the if statements

Re: Multiple IF statements in expression

Anonymous — Thu, 17 May 2018 05:28:06 GMT

Hi Xu,

Try this:

=if(WildMatch(Field Name,'*AAA*'),'AAA',

if(WildMatch(Field Name,'*BBB*'),'BBB',

if(WildMatch(Field Name,'*CCC*'),'CCC')))

hope it will work......

Re: Multiple IF statements in expression

nesonica — Thu, 23 Aug 2018 10:43:07 GMT

Hi guys,

thanks a lot for your supports! I now tried to solve this by ranging the "turnover" and filter "brands" but it does not really work. Could you please help me out?

// Pick the right brand

if(Vertrebskanal = "A" or "B",RightBrand,

// Turnover lower than 5000 is value "1"

if(Umsatz<5000,'1',

// Turnover lower than 20000 is value "2"

if(Umsatz<20000,'2',

// Turnover lower than 100000 is value "3"

if(Umsatz<100000,'3',

// Turnover lower than 500000 is value "4"

if(Umsatz<500000,'4',

// Turnover lower than 9999999 is value "5"

if(Umsatz<9999999 ,'5','0'))))))

as Umsatzwert_Kunden

Re: Multiple IF statements in expression

Demlet — Mon, 27 Aug 2018 17:58:10 GMT

Hi Nazih,

This wont work for the same reasons I described in an earlier comment to Mohammed.

The If Conditions are overlapping. For example "Umsatz<5000", '1'. This If statement could also equal 2, 3, 4, or 5, since it is less than 20000, 100000, 500000, and 99999999. You have to specify the exact range so each IF statement is definitive and can't equal any of the other values.

IF(Vertrebskanal = 'A' OR Vertrebskanal = 'B', 'RightBrand',

IF(Umsatz < '5000', '1',

IF(Umsatz < '20000' AND Umsatz > '5000', '2',

IF(Umsatz < '100000' AND Umsatz > '20000', '3',

IF(Umsatz < '500000' AND Umsatz > '100000', '4',

IF(Umsatz < '9999999' AND Umsatz > '500000', '5', 0)))))) AS Umsatzwert_Kunden

Hope this helps.

-Derek

Re: Multiple IF statements in expression

nesonica — Tue, 28 Aug 2018 13:49:00 GMT

Hi Derek,

thanks so much ! just used this and it worked for me. I only failed to set the variable "RightBrand". In case of a different expression, i want to pick this "RightBrand" as a filter, you know? 🙂

IF(Vertriebskanal = 'A' OR Vertriebskanal = 'B',

IF(Umsatz < '0', '1',

IF(Umsatz < '20000' AND Umsatz > '5000', '2',

IF(Umsatz < '100000' AND Umsatz > '20000', '3',

IF(Umsatz < '500000' AND Umsatz > '100000', '4',

IF(Umsatz < '9999999' AND Umsatz > '500000', '5', 0))))))

Re: Multiple IF statements in expression

Demlet — Wed, 29 Aug 2018 17:06:10 GMT

This should work.

IF((Vertriebskanal = 'A' OR Vertriebskanal = 'B'), 'RightBrand',

IF(Umsatz < '5000', '1',

IF((Umsatz < '20000' AND Umsatz > '5000'), '2',

IF((Umsatz < '100000' AND Umsatz > '20000'), '3',

IF((Umsatz < '500000' AND Umsatz > '100000'), '4',

IF((Umsatz < '9999999' AND Umsatz > '500000'), '5', 0)))))) AS Umsatzwert_Kunden

I forgot to add parenthesis. If there are multiple conditions that need to be met within an IF, then there needs to be another set of parenthesis grouping them together.

For example multiple OR/AND in an IF statement.

IF(((Vertriebskanal = 'A' OR Vertriebskanal = 'B') AND (SomeField='C' OR SomeField='D')), 'RightBrand', 'WrongBrand')