Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
naveenchellaara
Contributor III
Contributor III

Multiple IF statements in expression

Hi

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)

15 Replies
xz103389
Contributor
Contributor

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

sara_8154
Creator
Creator

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......

nesonica
Contributor III
Contributor III

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

Demlet
Contributor III
Contributor III

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

nesonica
Contributor III
Contributor III

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))))))

Demlet
Contributor III
Contributor III

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')