How to seperate New & renewals based on policy number!

[nevilledhamsiri]

Dear all,

Hope you may help me to achieve what I really needs which explain in the attached Excel.

Thanks

Neville

[m_woolf]

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,

[nevilledhamsiri]

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 (ooxml, embedded labels, table is Sheet1);

Regds

Neville

[nevilledhamsiri]

Appreciate if one of our member refer this & work out a solution!

Regds

Neville

[nevilledhamsiri]

Dear All,

Please do look in to this. I dying waiting here for an answer!

Regds

Neville

[sunny_talwar · Accepted solution]

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);

[nevilledhamsiri]

Dear Sunny!

It was absolutely fine. Thank you very much for the solution provided for one of my long pending issue.

Regds

Neville