PIVOT TABLE

[upaliwije]

Hi Friends

I am doing an analysis of Insurance related data and sample of my data is given below

POLICY_NO	YEAR	PERIOD_FROM	PERIOD_TO	NEW_REN	PREMIUM
NI00111C000039	2012	10-Feb-2012	09-Feb-2013	REN	18,212
NI00111C000039	2013	10-Feb-2013	09-Feb-2014	REN	17,372
NI00111C000039	2014	10-Feb-2014	09-Feb-2015	REN	16,278
NI00131A000028	2013	13-May-2013	12-May-2014	NEW	59,793
NI00131A000028	2014	13-May-2014	12-May-2015	REN	29,782
NI00141A0000041	2014	06-Oct-2014	05-Oct-2015	NEW	28,311
NI00141A0000041	2015	06-Oct-2015	05-Oct-2016	REN	27,205
NI00121F000014	2012	06-Mar-2012	05-Mar-2013	NEW	13,969
NI00121F000014	2013	06-Mar-2013	05-Mar-2014	REN	12,728
NI00121F000014	2014	06-Mar-2014	05-Mar-2015	REN	11,885
NI00121F000014	2015	06-Mar-2015	05-Mar-2016	REN	11,619
NI00111D000019	2012	31-May-2012	30-May-2013	REN	72,310
NI00111D000019	2013	31-May-2013	30-May-2014	REN	69,927
NI00141C0000106	2014	13-Oct-2014	12-Oct-2015	NEW	35,030
NI00141C0000106	2015	13-Oct-2015	12-Oct-2016	REN	33,205
NI00121D000053	2012	23-May-2012	22-May-2013	NEW	39,059
NI00121D000053	2013	23-May-2013	22-May-2014	REN	35,262

I want to summaries these policies into a table shown below

YEAR		2012	2013	2014	2015
2012	NEW	2
	REN	2	4	2	1
2013	NEW		1
	REN			1
2014	NEW			2
	REN				2
2015	NEW
	REN
		4	5	5	3

If I am to explain my requirement

4 polices done in 2012 come under  2 New and 2 Renewal 

Those 4  are renewed in 2013 and 1 new policy done in 2013

Out of 4 policies done in 2012 only 2 are renewed in 2014 and 1  in 2015

2 new policies are done in 2014 and 1 policy is renewed from 2013

..................

Hope you understand my requirement.

Kindly help me to tabulate above Requirement in QV document. Appreciate  If sample QV doc  is attched

[shamitshah]

Please see attached.

[Kushal_Chawda · Accepted solution]

Data:

LOAD * Inline [

POLICY_NO,    YEAR,    PERIOD_FROM,    PERIOD_TO,    NEW_REN,    PREMIUM

NI00111C000039,    2012,    10-Feb-2012,    09-Feb-2013,    REN,    18,212

NI00111C000039,    2013,    10-Feb-2013,    09-Feb-2014,    REN,    17,372

NI00111C000039,    2014,    10-Feb-2014,    09-Feb-2015,    REN,    16,278

NI00131A000028,    2013,    13-May-2013,    12-May-2014,    NEW,    59,793

NI00131A000028,    2014,    13-May-2014,    12-May-2015,    REN,    29,782

NI00141A0000041,    2014,    06-Oct-2014,    05-Oct-2015,    NEW,    28,311

NI00141A0000041,    2015,    06-Oct-2015,    05-Oct-2016,    REN,    27,205

NI00121F000014,    2012,    06-Mar-2012,    05-Mar-2013,    NEW,    13,969

NI00121F000014,    2013,    06-Mar-2013,    05-Mar-2014,    REN,    12,728

NI00121F000014,    2014,    06-Mar-2014,    05-Mar-2015,    REN,    11,885

NI00121F000014,    2015,    06-Mar-2015,    05-Mar-2016,    REN,    11,619

NI00111D000019,    2012,    31-May-2012,    30-May-2013,    REN,    72,310

NI00111D000019,    2013,    31-May-2013,    30-May-2014,    REN,    69,927

NI00141C0000106,    2014,    13-Oct-2014,    12-Oct-2015,    NEW    ,35,030

NI00141C0000106,    2015,    13-Oct-2015,    12-Oct-2016,    REN,    33,205

NI00121D000053,    2012,    23-May-2012,    22-May-2013,    NEW,    39,059

NI00121D000053,    2013,    23-May-2013,    22-May-2014,    REN,    35,262 ];

New:

NoConcatenate

LOAD *,

if((RowNo()=1 or POLICY_NO<>Previous(POLICY_NO)) ,YEAR,

if(POLICY_NO=Previous(POLICY_NO) , Peek('YEAR1'))) as YEAR1

Resident Data

Order by POLICY_NO,YEAR asc;

DROP Table Data;

Create Pivot Table

Dimensions:

YEAR1,

NEW_REN,

YEAR

Expression:

Count(POLICY_NO)

[upaliwije]

Thanks Kush

One more help from You. same If I want to tabulate policies relating to a particular but were not renewed in subsequent years how can I script it please

[Kushal_Chawda]

If there is One New policy created in 2013 and which is not renewed in 2014, you need to list our this policy. this is what you mean?

[upaliwije]

I want to show number of policies that were not renewed in the same table as follows

YEAR	NEW_REN	YEAR	2012	2013		2014		2015
			R	R	NR	R	NR	R	NR
2012	NEW		2
2012	REN		2	4		2		1
2013	NEW			1
2013	REN					1
2014	NEW					2
2014	REN							2
Total			4	5		5		3

[Kushal_Chawda]

Output is not clear to me. Can you send the output in excel?

[upaliwije]

It is like this

There were 4 policies in 2012 (new 2+ ren2)

in 2013 all 4 policies were renewed therefore not renewed policies is 0

In 2014 out 4 policies in 2012 only 2 were renewed balance 2 fall into not renewed catagory

In 2015 1 is renewed and 3 not renewed

Hope u understand my need

below is the sample

YEAR	NEW_REN	2012	2013		2014		2015
Renewed	Renewed	Not Renewed	Renewed	Not Renewed	Renewed	Not Renewed
2012	NEW	2
2012	REN	2	4	0	2	2	1	3
2013	NEW		1
2013	REN				1	0		1
2014	NEW				2
2014	REN						2
Total		4	5		5		3

Pls help

[upaliwije]

Hi Kush

Hope my illustration will help you find an answer my issue. Will you please help me with a sloution