Avoid Multiple If's

[soha1902]

Hi All,

Can any one suggest me how should I avoid multiple if's in my load script. At present my script looks like below :

Load
if((Today() - Fdate) < -2562,'>-7 Yrs',
if((Today() - Fdate) <= -2196,'-7 Yrs',
if((Today() - Fdate) <= -1830,'-6 Yrs',
if((Today() - Fdate) <= -1464,'-5 Yrs',
if((Today() - Fdate) <= -1098,'-4 Yrs',
if((Today() - Fdate) <= -732,'-3 Yrs',
if((Today() - Fdate) <= -366,'-2 Yrs',
if((Today() - Fdate) <=0,'-1 Yr',
if((Today() - Fdate) <= 366,'1 Yr',
if((Today() - Fdate) <= 732,'2 Yrs',
if((Today() - Fdate) <= 1098,'3 Yrs',
if((Today() - Fdate) <= 1464,'4 Yrs',
if((Today() - Fdate) <= 1830,'5 Yrs',
if((Today() - Fdate) <= 2196,'6 Yrs',
if((Today() - Fdate) <= 2562,'7 Yrs',
if((Today() - Fdate) > 2562,'>7 Yrs','>-7 Yrs')))))))))))))))) as UpdateFdate

How Should I optimize this code.

Thannks

[sunny_talwar]

May be this:

Load
If((Today() - Fdate) < -2562,'>-7 Yrs',

If((Today() - Fdate) >  2562, '>7 Yrs', Ceil(fabs(Today() - Fdate)/366) * If((Today() - Fdate) < 0, -1, 1) & ' Yrs')) as UpdateFdate

I like Stefan's Sign() function.

Load
If((Today() - Fdate) < -2562,'>-7 Yrs',

If((Today() - Fdate) >  2562, '>7 Yrs', Sign(Today() - Fdate) & Ceil(fabs(Today() - Fdate)/366)  & ' Yrs')) as UpdateFdate

[swuehl]

Maybe something like

LOAD

     Sign(Today() - Fdate) & (div(Today()-Fdate, 366)+1) & 'Yr(s)' as UpdateFdate

probably needs some enhancements, but could get you started