Multiple IF statements in expression

[naveenchellaara]

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)

[Anonymous]

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

[Anonymous]

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]

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]

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]

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]

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