topic Multiple nested if statements in QlikView

Multiple nested if statements

Sun, 01 Mar 2015 11:46:29 GMT

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.

Re: Multiple nested if statements

mightyqlikers — Mon, 02 Mar 2015 05:44:31 GMT

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

Re: Multiple nested if statements

jagan — Mon, 02 Mar 2015 06:00:30 GMT

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.

Re: Multiple nested if statements

timanshu — Mon, 02 Mar 2015 10:09:34 GMT

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

marcus_sommer — Mon, 02 Mar 2015 11:02:54 GMT

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

Mon, 02 Mar 2015 11:35:57 GMT

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

rwunderlich — Mon, 02 Mar 2015 16:22:38 GMT

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

Peter_Cammaert — Mon, 02 Mar 2015 16:30:41 GMT

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

Roop — Mon, 02 Mar 2015 16:36:15 GMT

A bit simpler:

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

Hope this helps

Re: Multiple nested if statements

jonnathanr — Mon, 02 Mar 2015 18:51:21 GMT

I have a similar problem

Re: Multiple nested if statements

timanshu — Tue, 03 Mar 2015 07:14:14 GMT

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

jagan — Tue, 03 Mar 2015 07:25:48 GMT

Hi Andre,

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

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.

Re: Multiple nested if statements

timanshu — Tue, 03 Mar 2015 07:41:39 GMT

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

marcus_sommer — Tue, 03 Mar 2015 08:14:52 GMT

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

- Marcus

Re: Multiple nested if statements

berndjaegle — Tue, 03 Mar 2015 08:42:35 GMT

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.

Re: Multiple nested if statements

jagan — Tue, 03 Mar 2015 08:44:40 GMT

Hi,

In that case use this

Groupings2:

Load *,

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

timanshu — Tue, 03 Mar 2015 09:05:25 GMT

Hi,

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

Just a small change to eradicate this:

Groupings2:

Load *,

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

marcus_sommer — Tue, 03 Mar 2015 09:08:03 GMT

See above and use fabs().

Re: Multiple nested if statements

timanshu — Tue, 03 Mar 2015 09:29:50 GMT

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

marcus_sommer — Tue, 03 Mar 2015 09:44:29 GMT

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