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

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.

jagan
Luminary Alumni
Luminary Alumni

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.

timanshu
Creator III
Creator III

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.

marcus_sommer

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

- Marcus

berndjaegle
Creator II
Creator II

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.

jagan
Luminary Alumni
Luminary Alumni

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.

timanshu
Creator III
Creator III

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;

marcus_sommer

See above and use fabs().

timanshu
Creator III
Creator III

Hi Marcus,

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

marcus_sommer

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