23 Replies Latest reply: Mar 3, 2015 6:18 AM by Timanshu Virmani

# Multiple nested if statements

Hi all

Groupings2:

If(0 <= seats_reamaining <= 10 , 'A',

If(10 < seats_reamaining <= 20 , 'B',

If(20 < seats_reamaining <= 30 , 'C',

If(30 < seats_reamaining <= 40 , 'D',

If(40 < seats_reamaining <= 50 , 'E',

If(50 < seats_reamaining <= 60 , 'F',

If(60 < seats_reamaining <= 70 , 'G',

If(70 < seats_reamaining <= 80 , 'H',

If(80 < seats_reamaining <= 90 , 'I',

If(90 < seats_reamaining <= 100 , 'J',

If(100 < seats_reamaining <= 110 , 'K',

If(110 < seats_reamaining <= 120 , 'L',

If(120 < seats_reamaining <= 130 , 'M',

If(130 < seats_reamaining <= 140 , 'N',

If(140 < seats_reamaining <= 150 , 'O',

If(150 < seats_reamaining <= 160 , 'P',

If(160 < seats_reamaining <= 170 , 'Q',

If(170 < seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

Resident Groupings1;

I keep getting the following error:

Error in expression:

')' expected

I don't understand this as the correct number of parenthesis is included to close all the if statements. Any help will be greatly appreciated.

ps I'm fairly new to QV.

• ###### Re: Multiple nested if statements

Hi Andre,

try like this

if(seats_remaining>0 and seats_remaining <=10,'A',

if(seats_remaining>10 and seats_remaining <=20,'B','Cannot Calculate')) as Seat_Class

resident Groups1;

Regards

samwisegamzee

• ###### Re: Multiple nested if statements

Hi,

Use this expression

Groupings2:

If(seats_reamaining <= 10 , 'A',

If(seats_reamaining <= 20 , 'B',

If(seats_reamaining <= 30 , 'C',

If(seats_reamaining <= 40 , 'D',

If(seats_reamaining <= 50 , 'E',

If(seats_reamaining <= 60 , 'F',

If(seats_reamaining <= 70 , 'G',

If(seats_reamaining <= 80 , 'H',

If(seats_reamaining <= 90 , 'I',

If(seats_reamaining <= 100 , 'J',

If(seats_reamaining <= 110 , 'K',

If(seats_reamaining <= 120 , 'L',

If(seats_reamaining <= 130 , 'M',

If(seats_reamaining <= 140 , 'N',

If(seats_reamaining <= 150 , 'O',

If(seats_reamaining <= 160 , 'P',

If(seats_reamaining <= 170 , 'Q',

If(seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

Resident Groupings1;

Regards,

Jagan.

• ###### Re: Multiple nested if statements

Hi Andre,

Qlikview does not understand the comparison between values as described in above load statement provided by you.

To make that logic understood by Qlikview,  Just Paste this instead of provided by you :

Groupings2:

If(0 <= seats_reamaining and seats_reamaining <= 10 , 'A',

If(10 < seats_reamaining and seats_reamaining <= 20 , 'B',

If(20 < seats_reamaining and seats_reamaining <= 30 , 'C',

If(30 < seats_reamaining and seats_reamaining <= 40 , 'D',

If(40 < seats_reamaining and seats_reamaining <= 50 , 'E',

If(50 < seats_reamaining and seats_reamaining <= 60 , 'F',

If(60 < seats_reamaining and seats_reamaining <= 70 , 'G',

If(70 < seats_reamaining and seats_reamaining <= 80 , 'H',

If(80 < seats_reamaining and seats_reamaining <= 90 , 'I',

If(90 < seats_reamaining and seats_reamaining <= 100 , 'J',

If(100 < seats_reamaining and seats_reamaining <= 110 , 'K',

If(110 < seats_reamaining and seats_reamaining <= 120 , 'L',

If(120 < seats_reamaining and seats_reamaining <= 130 , 'M',

If(130 < seats_reamaining and seats_reamaining <= 140 , 'N',

If(140 < seats_reamaining and seats_reamaining <= 150 , 'O',

If(150 < seats_reamaining and seats_reamaining <= 160 , 'P',

If(160 < seats_reamaining and seats_reamaining <= 170 , 'Q',

If(170 < seats_reamaining and seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

Resident Groupings1;

• ###### Re: Multiple nested if statements

Hi Timanshu

Thanks for this, it worked perfectly. I come from a SAS background so this is how I have always understood dual constraints.

• ###### Re: Multiple nested if statements

Hi Andre,

Your Welcome. You can mark the answer as correct if it solved your purpose. Not only this question,but for every question you put up. This will help other peoples to find the answers as if they can have the same query.

• ###### Re: Multiple nested if statements

Hi Andre,

Did you tried this simple expression?  No need of AND and many conditions

Groupings2:

If(seats_reamaining <= 10 , 'A',

If(seats_reamaining <= 20 , 'B',

If(seats_reamaining <= 30 , 'C',

If(seats_reamaining <= 40 , 'D',

If(seats_reamaining <= 50 , 'E',

If(seats_reamaining <= 60 , 'F',

If(seats_reamaining <= 70 , 'G',

If(seats_reamaining <= 80 , 'H',

If(seats_reamaining <= 90 , 'I',

If(seats_reamaining <= 100 , 'J',

If(seats_reamaining <= 110 , 'K',

If(seats_reamaining <= 120 , 'L',

If(seats_reamaining <= 130 , 'M',

If(seats_reamaining <= 140 , 'N',

If(seats_reamaining <= 150 , 'O',

If(seats_reamaining <= 160 , 'P',

If(seats_reamaining <= 170 , 'Q',

If(seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))) as Seat_Class

Resident Groupings1;

Regards,

Jagan.

• ###### Re: Multiple nested if statements

Hi Jagan, Yes this will too work if we have only positive values.  But if we have negative values then this expression will give 'A' but if we need to display cannot calculate then we have to use that lengthier code.  Choose depending on situation.

• ###### Re: Multiple nested if statements

In this case you could use fabs() which returned a positive value.

- Marcus

• ###### Re: Multiple nested if statements

Hi,

In that case use this

Groupings2:

If(seats_reamaining >= 0,

If( seats_reamaining <= 10 , 'A',

If(seats_reamaining <= 20 , 'B',

If(seats_reamaining <= 30 , 'C',

If(seats_reamaining <= 40 , 'D',

If(seats_reamaining <= 50 , 'E',

If(seats_reamaining <= 60 , 'F',

If(seats_reamaining <= 70 , 'G',

If(seats_reamaining <= 80 , 'H',

If(seats_reamaining <= 90 , 'I',

If(seats_reamaining <= 100 , 'J',

If(seats_reamaining <= 110 , 'K',

If(seats_reamaining <= 120 , 'L',

If(seats_reamaining <= 130 , 'M',

If(seats_reamaining <= 140 , 'N',

If(seats_reamaining <= 150 , 'O',

If(seats_reamaining <= 160 , 'P',

If(seats_reamaining <= 170 , 'Q',

If(seats_reamaining <= 180 , 'R', 'Cannot Calculate'))))))))))))))))))) as Seat_Class

Resident Groupings1;

Regards,

Jagan.

• ###### Re: Multiple nested if statements

Hi,

This is good. But, For negative values, this will give null.

Just a small change to eradicate this:

Groupings2:

If(seats_reamaining >= 0,

If( seats_reamaining <= 10 , 'A',

If(seats_reamaining <= 20 , 'B',

If(seats_reamaining <= 30 , 'C',

If(seats_reamaining <= 40 , 'D',

If(seats_reamaining <= 50 , 'E',

If(seats_reamaining <= 60 , 'F',

If(seats_reamaining <= 70 , 'G',

If(seats_reamaining <= 80 , 'H',

If(seats_reamaining <= 90 , 'I',

If(seats_reamaining <= 100 , 'J',

If(seats_reamaining <= 110 , 'K',

If(seats_reamaining <= 120 , 'L',

If(seats_reamaining <= 130 , 'M',

If(seats_reamaining <= 140 , 'N',

If(seats_reamaining <= 150 , 'O',

If(seats_reamaining <= 160 , 'P',

If(seats_reamaining <= 170 , 'Q',

If(seats_reamaining <= 180 , 'R', 'Cannot Calculate')))))))))))))))))),'Cannot Calculate') as Seat_Class

Resident Groupings1;

• ###### Re: Multiple nested if statements

See above and use fabs().

• ###### Re: Multiple nested if statements

Hi Marcus,

fabs() will make the value positive and then it will come under any of groups 'A' to 'R' , which is not correct.

• ###### Re: Multiple nested if statements

I understand your remark about negative values that they should be handled like positive values then otherwise all negatives will return "A". What should be the result from negative values NULL, 'Cannot Calculate' or ....

- Marcus

• ###### Re: Multiple nested if statements

Hi,

As per the Question by @Andre, It should be  'Cannot Calculate'

• ###### Re: Multiple nested if statements

....

If(seats_reamaining < 0, 'Cannot Calculate'

If( seats_reamaining <= 10 , 'A',

....

• ###### Re: Multiple nested if statements

Hi Jagan,

Here

If(seats_reamaining >= 0,

If( seats_reamaining <= 10 , 'A',

If(seats_reamaining <= 20 , 'B',

If(seats_reamaining <= 30 , 'C',

If(seats_reamaining <= 40 , 'D',

If(seats_reamaining <= 50 , 'E',

If(seats_reamaining <= 60 , 'F',

If(seats_reamaining <= 70 , 'G',

If(seats_reamaining <= 80 , 'H',

If(seats_reamaining <= 90 , 'I',

If(seats_reamaining <= 100 , 'J',

If(seats_reamaining <= 110 , 'K',

If(seats_reamaining <= 120 , 'L',

If(seats_reamaining <= 130 , 'M',

If(seats_reamaining <= 140 , 'N',

If(seats_reamaining <= 150 , 'O',

If(seats_reamaining <= 160 , 'P',

If(seats_reamaining <= 170 , 'Q',

If(seats_reamaining <= 180 , 'R', 'Cannot Calculate'))))))))))))))))))) as Seat_Class

if i am using above

If( seats_reamaining <= 10 , 'A', --- will it take only below 10 right?

but evry where 0-10 is repeating ?

• ###### Re: Multiple nested if statements

Hi Shravan,

If(seats_reamaining <= 20 , 'B',      --- it will take 11 to 20 , as 10 will come in --    If( seats_reamaining <= 10 , 'A',

• ###### Re: Multiple nested if statements

I suggest to replace the nested if-loops completely through a mapping-approach. Either directly with a mapping-table or with a combined pick(match()) function:

http://community.qlik.com/message/354694#354694

http://community.qlik.com/message/593232#593232

- Marcus

• ###### Re: Multiple nested if statements

I second Marcus' suggestion. Just to illustrate the idea: (only filled out through C, but you get the pattern).

GroupMap:

10, A

20, B

30, C

]

;

ApplyMap('GroupMap', ceil(seats_remaining,10, 'Cannot Calculate') as Seat_Class

Resident Groupings1;

If your values really are like in the example (A,B,C.. and split by 10) then you could also just calculate the Group like:

if(seats_remaining > 180, 'Cannot Calculate', chr(65+div(seats_remaining,10)) as Seat_Class

-Rob

http://robwunderlich.com

• ###### Re: Multiple nested if statements

And if at any time in the future, you are facing irregular category specifications, use an external (xlsx) table and INTERVALMATCH LOAD to assign Seat_Classes without restrictions.

Peter

• ###### Re: Multiple nested if statements

A bit simpler:

if(seats/10 > 18,
'Cannot Calculate',
chr(seats/10+65)
) as Seat_Class

Hope this helps

• ###### Re: Multiple nested if statements

I have a similar problem

• ###### Re: Multiple nested if statements

I always prefer the mapping appoach. To define my mappings I use an external table, like xls or txt. Simmple to maintain and add values, when needed.