Dual if statement with multiple conditions

[#1]

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!

[Anil_Babu_Samineni · Accepted solution]

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

[#3]

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

[oknotsen]

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.

[Anil_Babu_Samineni]

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

[#6]

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.

[Anil_Babu_Samineni]

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

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

Resident NTMs;

[#8]

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

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;

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?

[oknotsen]

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

[#10]

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