
Re: Dual if statement with multiple conditions
Anil Babu Apr 10, 2017 3:51 AM (in response to vivek prasad)May be this? I am assuming these are the fields [90+(Rent8000)], [100+(Rent8000)] & [110+(Rent8000)]
If(d<30, ([90+(Rent8000)]/1000)*3, If(d>=30 and d<60, ([100+(Rent8000)]/1000)*3, If(d >= 60 and d<90, ([110+(Rent8000)]/1000)*3))) as FieldName
Or else you could define as this
If(d<30, (90+(Rent8000)/1000)*3, If(d>=30 and d<60, (100+(Rent8000)/1000)*3, If(d >= 60 and d<90, (110+(Rent8000)/1000)*3))) as FieldName // I am assuming if this is not a field then use this

Re: Dual if statement with multiple conditions
vivek prasad Apr 10, 2017 4:10 AM (in response to Anil Babu )Thanks Anil, that worked. It was the latter, 'not a field'.

Re: Dual if statement with multiple conditions
Anil Babu Apr 10, 2017 4:51 AM (in response to vivek prasad)Great. And, I would high recommend you to don't duplicate same thread.


Re: Dual if statement with multiple conditions
Onno van Knotsenburg Apr 10, 2017 4:24 AM (in response to Anil Babu )That ifstatement can be written more efficient by excluding the parts in the nested if that are already covered by the earlier ifstatement.
If(d<30, ([90+(Rent8000)]/1000)*3, If(d>=30 and d<60, ([100+(Rent8000)]/1000)*3, If(d >= 60 and d<90, ([110+(Rent8000)]/1000)*3))) as FieldName
... does the same as...
If(d<30, ([90+(Rent8000)]/1000)*3, If(d<60, ([100+(Rent8000)]/1000)*3, If(d<90, ([110+(Rent8000)]/1000)*3))) as FieldName
... except the 2nd is more efficient.


Re: Dual if statement with multiple conditions
vivek prasad Apr 11, 2017 2:32 AM (in response to vivek prasad)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+(Rent8000)/1000)*3, If(d>=30 and d<60, (100+(Rent8000)/1000)*3, If(d >= 60 and d<90, (110+(Rent8000)/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 Apr 11, 2017 3:04 AM (in response to vivek prasad)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+(Rent8000)/1000)*3, If(d>=30 and d<60, (100+(Rent8000)/1000)*3, If(d >= 60 and d<90, (110+(Rent8000)/1000)*3))) as FieldName
Resident NTMs;

Re: Dual if statement with multiple conditions
vivek prasad Apr 11, 2017 6:35 AM (in response to Anil Babu )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+((Rent8000)/1000)*3), If(d>=30 and d<60, (100+((Rent8000)/1000)*3), If(d >= 60 and d<90, (110+((Rent8000)/1000)*3),If(d >= 90 and d<120, (120+((Rent8000)/1000)*3),If(d >= 120 and d<150, (140+((Rent8000)/1000)*3),If(d >= 150 and d<180, (150+((Rent8000)/1000)*3),If(d >= 180 and d<210, (160+((Rent8000)/1000)*3),(170+((Rent800)/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+((Rent8000)/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
Onno van Knotsenburg Apr 11, 2017 7:32 AM (in response to vivek prasad)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
vivek prasad Apr 11, 2017 8:17 AM (in response to Onno van Knotsenburg )
Re: Dual if statement with multiple conditions
Onno van Knotsenburg Apr 11, 2017 8:36 AM (in response to vivek prasad)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
Anil Babu Apr 11, 2017 9:24 AM (in response to vivek prasad)LOAD
d,
Rent,
If(d<30, (90+((Rent8000)/1000)*3), If(d>=30 and d<60, (100+((Rent8000)/1000)*3), If(d >= 60 and d<90, (110+((Rent8000)/1000)*3),If(d >= 90 and d<120, (120+((Rent8000)/1000)*3),If(d >= 120 and d<150, (140+((Rent8000)/1000)*3),If(d >= 150 and d<180, (150+((Rent8000)/1000)*3),If(d >= 180 and d<210, (160+((Rent8000)/1000)*3),(170+((Rent800)/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
vivek prasad Apr 12, 2017 2:29 AM (in response to Anil Babu )Thanks for pointing out, fixed that too





Re: Dual if statement with multiple conditions
vivek prasad Apr 11, 2017 9:04 AM (in response to vivek prasad)Sorted now. There was some issues because of synthetic table. Thanks