Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem in Reload

Hello everyone,   i'm facing some problems when reloading my script,it took large time to finish it,i guess because of my function in my table [Earned Premium]:
if((if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))<=17,'0-17',if((if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))>=18 and (if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))<=35,'18-35',if((if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))>=36 and (if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))<=45,'36-45',if((if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))>=46 and (if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))<=55,'46-55',if((if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))>=56 and (if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))<=60,'56-60',if((if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2008' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '04/30/2009',2008,if(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')>='05/01/2009' and date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY') <= '12/31/2010',2009))
-
year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')))>=61 and (year(date(Makedate(right(EFFDATE, 4),if(mid(EFFDATE, 3,3)='JAN',01,if(mid(EFFDATE, 3,3)='FEB',02,if(mid(EFFDATE, 3,3)='MAR',03,if(mid(EFFDATE, 3,3)='APR',04,if(mid(EFFDATE, 3,3)='MAY',05,if(mid(EFFDATE, 3,3)='JUN',06,if(mid(EFFDATE, 3,3)='JUL',07,if(mid(EFFDATE, 3,3)='AUG',08,if(mid(EFFDATE, 3,3)='SEP',09,if(mid(EFFDATE, 3,3)='OCT',10,if(mid(EFFDATE, 3,3)='NOV',11,if(mid(EFFDATE, 3,3)='DEC',12,00)))))))))))),Left(EFFDATE, 2)), 'DD-MM-YYYY')) - year(DOB))<=65,'61-65','66-Above')))))) as [Age Group]
so i added another table using "resident"  and my script was:  Temp:
LOAD
//the function look up  below
RESIDENT [EarnedPremium];
but it didn't work ,itcouldn't find the field <EFFDATE> can anyone help me to solve it.
Thank You in advance.
3 Replies
Miguel_Angel_Baeyens

Hi,

First of all, there are some functions to use with dates, such as Age() that returns the number in years between two dates and Class() that returns the classification of a value regarding the parameters passed. It would be useful if you told us what do you want to get based on yoru EFFDATE field. What does those "-" signs in your script mean? Are they there on purpose?

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

hello,

well, EFFDATE field means the effective date and "-" is a substraction ,our purpose in this function is to get the age brackets field for example the field is : age bracket and the values are : 0-17 ,18-35 ...    so in this function we substract EFFDATE or the effective date from the DOB witch means the date of birth

to get the age brackets.

regards.

Miguel_Angel_Baeyens

Hi,

Check the following and adapt it to your own data and field names

Data:

LOAD Date(EFFDATE) AS EFFDATE,

     Date(Date#(DOB, 'DDMMMYYYY')) AS DOB,

     Age(Date(EFFDATE), Date(Date#(DOB, 'DDMMMYYYY'))) AS AGE,

     If(Age(Date(EFFDATE), Date(Date#(DOB, 'DDMMMYYYY'))) <= 60, '<=60', If(Age(Date(EFFDATE), Date(Date#(DOB, 'DDMMMYYYY'))) > 65, '66-Above', '61-65')) AS AgeGroup

INLINE [

EFFDATE, DOB

1/5/2010, 10ENE1970

1/5/2010, 20JUN1988

1/5/2010, 23MAY1975

1/5/2010, 03AGO1978

1/5/2010, 03AGO1918

1/5/2010, 03AGO1944

];

Although this is loading INLINE (hardcoded values) you can use any other source.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica