Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

nevilledhamsiri
Valued Contributor

How to seperate New & renewals based on policy number!

Dear all,

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

Thanks

Neville

1 Solution

Accepted Solutions

Re: How to seperate New & renewals based on policy number!

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

Capture.PNG

6 Replies
mwoolf
Honored Contributor II

Re: How to seperate New & renewals based on policy number!

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
Valued Contributor

Re: How to seperate New & renewals based on policy number!

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
Valued Contributor

Re: How to seperate New & renewals based on policy number!

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

Regds

Neville

nevilledhamsiri
Valued Contributor

Re: How to seperate New & renewals based on policy number!

Dear All,

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

Regds

Neville

Re: How to seperate New & renewals based on policy number!

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

Capture.PNG

nevilledhamsiri
Valued Contributor

Re: How to seperate New & renewals based on policy number!

Dear Sunny!

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

Regds

Neville

Community Browser