topic Re: Active vs Terminated Policy in QlikView

Active vs Terminated Policy

Mon, 29 Sep 2014 14:17:59 GMT

Hi everyone, I have following table

I need to create a chart to show the number of policy Active vs Terminated year wise.I attached a sample file which has Two different bar chart,I want to show both two chart in single chart.anyone please help me in this.Its really urgent

Re: Active vs Terminated Policy

Anonymous — Mon, 29 Sep 2014 14:50:51 GMT

In the load script load the data into 2 separate tables:

Issue Data

Terminated Data

For both these tables rename the ...Year as just [Year] so they link to [Year] in your Tab-1 table

Re: Active vs Terminated Policy

MK_QSL — Mon, 29 Sep 2014 14:53:37 GMT

Fact:

Load

Customer & '|' & [Policy Id] as Key,

Customer,

[Policy Id],

Date(Date#([Issue date],'DD-MM-YY')) as [Issue date],

Date(Date#([Terminated Date],'DD-MM-YY')) as [Terminated Date]

Inline

[

Customer, Policy Id, Issue date, Terminated Date

AA, 11, 12-12-08, 12-12-13

AA, 12, 14-11-11, 14-11-16

AA, 13, 14-11-11, 14-12-14

AA, 15, 12-12-08, 12-12-13

BB, 11, 13-05-08, 12-12-13

BB, 11, 01-05-12, 14-11-16

BB, 12, 13-05-08, 13-05-11

BB, 12, 12-12-13, 12-12-13

BB, 13, 01-05-12, 01-05-16

CC, 11, 03-02-08, 03-02-14

CC, 11, 14-12-10, 14-12-14

CC, 12, 14-12-10, 13-05-11

CC, 12, 03-02-12, 01-05-16

CC, 14, 03-02-12, 03-02-14

];

IssueDate:

Mapping Load Key, [Issue date] Resident Fact;;

TermniateDate:

Mapping Load Key, [Terminated Date] Resident Fact;

DateBridge:

Load Key,'Issue' as DataType, ApplyMap('IssueDate',Key,'Unknown') as LinkedDate Resident Fact;

Load Key,'Terminate' as DataType, ApplyMap('TermniateDate',Key,'Unknown') as LinkedDate Resident Fact;

Join

Load LinkedDate, Year(LinkedDate) as LinkedYear Resident DateBridge;

EXIT Script;

Create a bar Chart

Dimension

LinkedYear

Expression

COUNT({<DataType = {'Issue'}>}DISTINCT [Policy Id])

COUNT({<DataType = {'Terminate'}>}DISTINCT [Policy Id])

Re: Active vs Terminated Policy

Mon, 29 Sep 2014 14:55:57 GMT

It show wrong data.. any other solution,its really urgent

Re: Active vs Terminated Policy

JonnyPoole — Mon, 29 Sep 2014 14:58:45 GMT

I changed your table structure slightly to have a Date (and Year) and a Type (terminated or issued).

This gives you the dimensions you need to do this.

Your chart dimensions will be Year and Type and the expression is count(distinct policy Id)

Data:

LOAD Customer,

[Issue date] as Date,

Issue_Year as Year,

[Policy Id],

'Issue' as Type

FROM

(biff, embedded labels, table is Sheet1$);

Concatenate (Data)

Load Customer,

[Terminated Date] as Date,

terminated_year as Year,

[Policy Id],

'Terminated' as Type

FROM

(biff, embedded labels, table is Sheet1$);

Re: Active vs Terminated Policy

Mon, 29 Sep 2014 15:01:52 GMT

the resulted chart didnt show correct value....

Re: Active vs Terminated Policy

Mon, 29 Sep 2014 15:05:57 GMT

I dont think this is the right way to do concatenation will increase the size of the table,My actual table has data more than 5 lacs,it will increase the size,and resulted chart value is not correct anyway..

Re: Active vs Terminated Policy

MK_QSL — Mon, 29 Sep 2014 15:25:10 GMT

What is the result you expected?

Provide in Straight Table please

Re: Re: Active vs Terminated Policy

JonnyPoole — Mon, 29 Sep 2014 15:26:24 GMT

There were many duplicate records in the source , i think because the YEAR field was meant to show all combinations.

I removed the duplication with the following script with load distinct and resident loads.

overall this is similar to a 'canonical date' approach which is very commonly used. here is the reference

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

As for data volumes, the table is longer but its mostly just repetitive information (same customer, policy and year) and less fields. Overall Uniqueness does not grow very much yielding a less impact to RAM then the number of rows suggest.

----------------------

temp:

LOAD

Customer,

// [Issue date] as Date,

Issue_Year as Year,

[Policy Id]

FROM

(biff, embedded labels, table is Sheet1$);

Data:

load Distinct

'Issue' as Type

resident temp;

drop table temp;

temp:

Load

Customer,

// [Terminated Date] as Date,

terminated_year as Year,

[Policy Id]

FROM

(biff, embedded labels, table is Sheet1$);

Concatenate (Data)

Load

Distinct

'Terminated' as Type

Resident temp;

drop table temp;