topic Re: Avoid Multiple If's in QlikView

Avoid Multiple If's

soha1902 — Tue, 02 Feb 2016 11:41:03 GMT

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

Re: Avoid Multiple If's

sunny_talwar — Tue, 02 Feb 2016 11:49:08 GMT

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

Re: Avoid Multiple If's

swuehl — Tue, 02 Feb 2016 11:49:19 GMT

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