
Re: Multiple nested if statements
sam wisegamzee Mar 2, 2015 12:44 AM (in response to Andre Nel)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 mohan rao appala Mar 2, 2015 1:00 AM (in response to Andre Nel)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 Virmani Mar 2, 2015 5:09 AM (in response to Andre Nel)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
Andre Nel Mar 2, 2015 6:35 AM (in response to Timanshu Virmani)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
Timanshu Virmani Mar 3, 2015 2:14 AM (in response to Andre Nel)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 mohan rao appala Mar 3, 2015 2:25 AM (in response to Andre Nel)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 Virmani Mar 3, 2015 2:41 AM (in response to jagan mohan rao appala )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 Mar 3, 2015 3:14 AM (in response to Timanshu Virmani)In this case you could use fabs() which returned a positive value.
 Marcus

Re: Multiple nested if statements
jagan mohan rao appala Mar 3, 2015 3:44 AM (in response to Timanshu Virmani)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 Virmani Mar 3, 2015 4:05 AM (in response to jagan mohan rao appala )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 Mar 3, 2015 4:08 AM (in response to Timanshu Virmani)See above and use fabs().

Re: Multiple nested if statements
Timanshu Virmani Mar 3, 2015 4:29 AM (in response to Marcus Sommer )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 Mar 3, 2015 4:44 AM (in response to Timanshu Virmani)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

Re: Multiple nested if statements
Timanshu Virmani Mar 3, 2015 4:47 AM (in response to Marcus Sommer )Hi,
As per the Question by @Andre, It should be 'Cannot Calculate'

Re: Multiple nested if statements
Marcus Sommer Mar 3, 2015 5:22 AM (in response to Timanshu Virmani)....
If(seats_reamaining < 0, 'Cannot Calculate'
If( seats_reamaining <= 10 , 'A',
....






Re: Multiple nested if statements
shravan kumar Mar 3, 2015 6:00 AM (in response to jagan mohan rao appala )Hi Jagan,
Here
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
if i am using above
If( seats_reamaining <= 10 , 'A',  will it take only below 10 right?
If(seats_reamaining <= 20 , 'B',  wat about this it will take 020 right?
but evry where 010 is repeating ?

Re: Multiple nested if statements
Timanshu Virmani Mar 3, 2015 6:18 AM (in response to shravan kumar)Hi Shravan,
If(seats_reamaining <= 20 , 'B',  it will take 11 to 20 , as 10 will come in  If( seats_reamaining <= 10 , 'A',







Re: Multiple nested if statements
Marcus Sommer Mar 2, 2015 6:02 AM (in response to Andre Nel)I suggest to replace the nested ifloops completely through a mappingapproach. Either directly with a mappingtable 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
Rob Wunderlich Mar 2, 2015 11:23 AM (in response to Marcus Sommer )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

Re: Multiple nested if statements
Peter Cammaert Mar 2, 2015 11:30 AM (in response to Rob Wunderlich )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
Rupert Cavendish Mar 2, 2015 11:36 AM (in response to Andre Nel)A bit simpler:
if(seats/10 > 18,
'Cannot Calculate',
chr(seats/10+65)
) as Seat_ClassHope this helps

Re: Multiple nested if statements
jonnathan rodrigues Mar 2, 2015 1:51 PM (in response to Andre Nel)I have a similar problem

Re: Multiple nested if statements
Bernd Jägle Mar 3, 2015 3:42 AM (in response to Andre Nel)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.