13 Replies Latest reply: Apr 12, 2017 2:29 AM by vivek prasad

# Dual if statement with multiple conditions

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.

Thanks!

• ###### Re: Dual if statement with multiple conditions

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

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

• ###### Re: Dual if statement with multiple conditions

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

• ###### Re: Dual if statement with multiple conditions

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

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:

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

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;

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;

• ###### Re: Dual if statement with multiple conditions

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;

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?

• ###### Re: Dual if statement with multiple conditions

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

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

• ###### Re: Dual if statement with multiple conditions

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

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

Thanks for pointing out, fixed that too

• ###### Re: Dual if statement with multiple conditions

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