Multiple nested if statements

[#1]

Hi all

Can someone please help me with the following load statement:

Groupings2:

Load *,

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.

[mightyqlikers]

Hi Andre,

try like this

load *,

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

[jagan]

Hi,

Use this expression

Groupings2:

Load *,

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.

[timanshu]

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:

Load *,

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;

[marcus_sommer]

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

[#6]

Hi Timanshu

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

[rwunderlich]

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

GroupMap:

MAPPING LOAD * INLINE [

10, A

20, B

30, C

]

;

LOAD *,

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://masterssummit.com

http://robwunderlich.com

[Peter_Cammaert]

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

[Roop]

A bit simpler:

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

Hope this helps

[jonnathanr]

I have a similar problem