Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
Hope you may help me to achieve what I really needs which explain in the attached Excel.
Thanks
Neville
May be this
Table:
LOAD *,
If(Year <= 2015, 'Renewals', If(Year >= 2017, 'New', If((Year([DATE REFUNDED])*12 + Month([DATE REFUNDED])) - (Year([PERIOD FROM])*12 + Month([PERIOD FROM])) > 12, 'Renewals', 'New'))) as Flag;
LOAD BR_CODE,
CLASS_CODE,
PRD_CODE,
POLICY_NUMBER,
INSURED,
[REFUND NO],
[DATE REFUNDED],
[PERIOD FROM],
[PERIOD TO],
PREMIUM,
Year(Date#(If(Len(POLICY_NUMBER) = 15, Mid(POLICY_NUMBER, 5, 2), Left(POLICY_NUMBER, 2)), 'YY')) as Year
FROM
[..\..\..\Downloads\seperation New & renewals.xlsx]
(ooxml, embedded labels, table is Sheet2);
If I understand your request, you want to produce the year from the Policy Number:
if(len(POLICY_NUMBER)=15,
MID(POLICY_NUMBER,5,2),left(POLICY_NUMBER,2)) as Year,
Hi Dear,
You have resolved my 1st issue. Thank you very much.That is how to separate Year from the policy number. Now my 2nd issue is how to pick new policies & renewal policies based on the Year. As I said below 2016 all policies to be treated as Renewals. 2017 policies to be treated as New. But 2016 policies to be separated between New & Renewals based on the Date refunded & Period from date. If (refund date-period from)>12 months, it should be Renewals. If (Date Refunded-Period from date)<12 months, it should be New.
Therefore I need you to consider above conditions & build up if statement in the script so that I can create a list box for New & Renewal policies
REFUND_REGISTER:
LOAD BR_CODE, CLASS_CODE, PRD_CODE, POLICY_NUMBER, if(len(POLICY_NUMBER)=15,
MID(POLICY_NUMBER,5,2),left(POLICY_NUMBER,2)) as Year, F5, INSURED, [REFUND NO], [DATE REFUNDED],
[PERIOD FROM], [PERIOD TO], PREMIUM
FROM
Regds
Neville
Appreciate if one of our member refer this & work out a solution!
Regds
Neville
Dear All,
Please do look in to this. I dying waiting here for an answer!
Regds
Neville
May be this
Table:
LOAD *,
If(Year <= 2015, 'Renewals', If(Year >= 2017, 'New', If((Year([DATE REFUNDED])*12 + Month([DATE REFUNDED])) - (Year([PERIOD FROM])*12 + Month([PERIOD FROM])) > 12, 'Renewals', 'New'))) as Flag;
LOAD BR_CODE,
CLASS_CODE,
PRD_CODE,
POLICY_NUMBER,
INSURED,
[REFUND NO],
[DATE REFUNDED],
[PERIOD FROM],
[PERIOD TO],
PREMIUM,
Year(Date#(If(Len(POLICY_NUMBER) = 15, Mid(POLICY_NUMBER, 5, 2), Left(POLICY_NUMBER, 2)), 'YY')) as Year
FROM
[..\..\..\Downloads\seperation New & renewals.xlsx]
(ooxml, embedded labels, table is Sheet2);
Dear Sunny!
It was absolutely fine. Thank you very much for the solution provided for one of my long pending issue.
Regds
Neville