Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple nested if statements

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.

23 Replies
mightyqlikers
Creator III
Creator III

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
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III

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

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner - Champion III
Partner - Champion III

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

A bit simpler:

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

Hope this helps

jonnathanr
Contributor III
Contributor III

I have a similar problem