# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
MVP

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

May be this

Table:

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;

CLASS_CODE,

PRD_CODE,

POLICY_NUMBER,

INSURED,

[REFUND NO],

[DATE REFUNDED],

[PERIOD FROM],

[PERIOD TO],

Year(Date#(If(Len(POLICY_NUMBER) = 15, Mid(POLICY_NUMBER, 5, 2), Left(POLICY_NUMBER, 2)), 'YY')) as Year

FROM

(ooxml, embedded labels, table is Sheet2);

6 Replies
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,

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],

FROM (ooxml, embedded labels, table is Sheet1);

Regds

Neville

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

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

MVP

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

May be this

Table:

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;

CLASS_CODE,

PRD_CODE,

POLICY_NUMBER,

INSURED,

[REFUND NO],

[DATE REFUNDED],

[PERIOD FROM],

[PERIOD TO],

Year(Date#(If(Len(POLICY_NUMBER) = 15, Mid(POLICY_NUMBER, 5, 2), Left(POLICY_NUMBER, 2)), 'YY')) as Year

FROM

(ooxml, embedded labels, table is Sheet2);

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