topic Re: Dual if statement with multiple conditions in QlikView

Dual if statement with multiple conditions

Mon, 10 Apr 2017 06:19:16 GMT

Hi I am trying to add multiple if conditions for the below task as hand:

if d<30

result=([90+(Rent-8000)]/1000)*3

else if 30<d<60

result=([100+(Rent-8000)]/1000)*3

else if 60<d<90

result=([110+(Rent-8000)]/1000)*3

I tried this using multiple if statement in the script editor LOAD. However, I get error if I try to use multiple conditions under one if statement.

Could any one please help me to work on the right approach towards this issue ?

Thanks!

Re: Dual if statement with multiple conditions

Anil_Babu_Samineni — Mon, 10 Apr 2017 07:51:26 GMT

May be this? I am assuming these are the fields [90+(Rent-8000)], [100+(Rent-8000)] & [110+(Rent-8000)]

If(d<30, ([90+(Rent-8000)]/1000)*3, If(d>=30 and d<60, ([100+(Rent-8000)]/1000)*3, If(d >= 60 and d<90, ([110+(Rent-8000)]/1000)*3))) as FieldName

Or else you could define as this

If(d<30, (90+(Rent-8000)/1000)*3, If(d>=30 and d<60, (100+(Rent-8000)/1000)*3, If(d >= 60 and d<90, (110+(Rent-8000)/1000)*3))) as FieldName // I am assuming if this is not a field then use this

Re: Dual if statement with multiple conditions

Mon, 10 Apr 2017 08:10:31 GMT

Thanks Anil, that worked. It was the latter, 'not a field'.

Re: Dual if statement with multiple conditions

oknotsen — Mon, 10 Apr 2017 08:24:47 GMT

That if-statement can be written more efficient by excluding the parts in the nested if that are already covered by the earlier if-statement.

If(d<30, ([90+(Rent-8000)]/1000)*3, If(d>=30 and d<60, ([100+(Rent-8000)]/1000)*3, If(d >= 60 and d<90, ([110+(Rent-8000)]/1000)*3))) as FieldName

... does the same as...

If(d<30, ([90+(Rent-8000)]/1000)*3, If(d<60, ([100+(Rent-8000)]/1000)*3, If(d<90, ([110+(Rent-8000)]/1000)*3))) as FieldName

... except the 2nd is more efficient.

Re: Dual if statement with multiple conditions

Anil_Babu_Samineni — Mon, 10 Apr 2017 08:51:38 GMT

Great. And, I would high recommend you to don't duplicate same thread.

Re: Dual if statement with multiple conditions

Tue, 11 Apr 2017 06:31:54 GMT

Thanks Guys.

Just below the above if code I am trying to check for another condition as below but its giving me error as <Field not found> :

Calculations:

LOAD

d,

If(d<30, (90+(Rent-8000)/1000)*3, If(d>=30 and d<60, (100+(Rent-8000)/1000)*3, If(d >= 60 and d<90, (110+(Rent-8000)/1000)*3))) as FieldName,

IF(FieldName<90,'Below AOP','Above AOP')as aop

Resident NTMs

Would appreciate any help or suggestion.

Re: Dual if statement with multiple conditions

Anil_Babu_Samineni — Tue, 11 Apr 2017 06:45:08 GMT

You can't do with in the fieldname for ref. May be use below

Calculations:

Load d, FieldName, IF(FieldName<90,'Below AOP','Above AOP') as aop;

LOAD

If(d<30, (90+(Rent-8000)/1000)*3, If(d>=30 and d<60, (100+(Rent-8000)/1000)*3, If(d >= 60 and d<90, (110+(Rent-8000)/1000)*3))) as FieldName

Resident NTMs;

Re: Dual if statement with multiple conditions

Tue, 11 Apr 2017 10:34:07 GMT

You are awesome Anil, this works flawless except I am getting multiple values in the FieldName for some strange reason.

Here is the code:

Calculations:

Load d, FieldName, IF(FieldName<90,'Below AOP','Above AOP') as aop;

LOAD

Rent,

If(d<30, (90+((Rent-8000)/1000)*3), If(d>=30 and d<60, (100+((Rent-8000)/1000)*3), If(d >= 60 and d<90, (110+((Rent-8000)/1000)*3),If(d >= 90 and d<120, (120+((Rent-8000)/1000)*3),If(d >= 120 and d<150, (140+((Rent-8000)/1000)*3),If(d >= 150 and d<180, (150+((Rent-8000)/1000)*3),If(d >= 180 and d<210, (160+((Rent-8000)/1000)*3),(170+((Rent-800)/1000)*3)))))))) as FieldName

Resident NTMs;

So now for the value of Rent=7000 and d=174, I am getting 10 different values for FieldName but considering the condition: it should be under If(d >= 150 and d<180, (150+((Rent-8000)/1000)*3) and as per calculation FieldName=147

but I am getting 10 different values for the same as :

FieldName

126

135

136.5

141

144

147

150

154.5

160.5

163.8

Any thoughts?

Re: Dual if statement with multiple conditions

oknotsen — Tue, 11 Apr 2017 11:32:02 GMT

Apparently d or Rent has 10 different values, so the result of your calculation is 10 different values.

Re: Dual if statement with multiple conditions

Tue, 11 Apr 2017 12:17:48 GMT

No there's just 1 value each for d and Rent:

Re: Dual if statement with multiple conditions

oknotsen — Tue, 11 Apr 2017 12:36:27 GMT

That screenshot is not showing me anything to prove to me that at the moment of doing a resident load on NTMs there are not multiple values of for either "d" or "Rent".

As a calculation does not randomly make up numbers (or at least not this basic calculation), I see no other explanation.

Re: Dual if statement with multiple conditions

Tue, 11 Apr 2017 13:04:43 GMT

Sorted now. There was some issues because of synthetic table. Thanks

Re: Dual if statement with multiple conditions

Anil_Babu_Samineni — Tue, 11 Apr 2017 13:24:43 GMT

LOAD

d,

Rent,

If(d<30, (90+((Rent-8000)/1000)*3), If(d>=30 and d<60, (100+((Rent-8000)/1000)*3), If(d >= 60 and d<90, (110+((Rent-8000)/1000)*3),If(d >= 90 and d<120, (120+((Rent-8000)/1000)*3),If(d >= 120 and d<150, (140+((Rent-8000)/1000)*3),If(d >= 150 and d<180, (150+((Rent-8000)/1000)*3),If(d >= 180 and d<210, (160+((Rent-8000)/1000)*3),(170+((Rent-800)/1000)*3)))))))) as FieldName

Resident NTMs;

Here, You have mentioned single 800 only. Is that is correct? or you may miss to add one more 0 like 8000

Re: Dual if statement with multiple conditions

Wed, 12 Apr 2017 06:29:34 GMT

Thanks for pointing out, fixed that too