Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Multiple nested if statements

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


MVP
MVP

Re: Multiple nested if statements

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

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:

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;

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

Not applicable

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

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

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

rupe
Valued Contributor

Re: Multiple nested if statements

A bit simpler:

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

Hope this helps

jonnathanr
New Contributor III

Re: Multiple nested if statements

I have a similar problem

Community Browser