topic Insurance Claims in QlikView

Insurance Claims

upaliwije — Wed, 22 Feb 2017 04:35:06 GMT

Hi Friends

I want to analyse and compare the Insurance Claims Paid data with Insurance Premium. In this I want to find out amount of claim paid during a policy period against the premium. In other words I have Premium data with corresponding Insurance Policy Periods. I want to know how to link insurance claim date which falls between policy period. Pls help to write the data load script.

Re: Insurance Claims

ramachandra_gee — Wed, 22 Feb 2017 04:44:42 GMT

do you have movement type for every claim in claim table

and notification date also available in claim data

Re: Insurance Claims

rahulpawarb — Wed, 22 Feb 2017 05:30:13 GMT

Hello Upali,

Trust that you are doing good!

As per my primary understanding you can make use of IntervalMatch functionality (refer below given link).

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/ScriptPrefixes/IntervalMatch.htm

It would be helpful if you could share the application with sample data.

Regards!

Rahul

Re: Insurance Claims

upaliwije — Wed, 22 Feb 2017 05:58:02 GMT

Hi Rahul

Thanks for your reply.

I attach h/w my sample data kindly advise me

Re: Insurance Claims

rahulpawarb — Wed, 22 Feb 2017 07:31:58 GMT

Hello Upali,

Please refer attached application file.

P.S.: You can encounter problem while reloading the data. Please change the Excel file location in FROM clause.

Regards!

Rahul

Re: Insurance Claims

upaliwije — Wed, 22 Feb 2017 08:11:49 GMT

Thanks,

I have an issue. What you can see from the attachment in My pivot table Premium is duplicated . How can I avoid it

Re: Insurance Claims

sasikanth — Wed, 22 Feb 2017 08:32:29 GMT

HI,

Try to use this

Inner Join

IntervalMatch (LOSS_DATE)

LOAD From_date, To_date

Resident premium;

PFA,

Re: Insurance Claims

upaliwije — Wed, 22 Feb 2017 08:40:08 GMT

I want to show premium only per policy period although more than one claim has occurred for the particular;la policy

Re: Insurance Claims

upaliwije — Wed, 22 Feb 2017 10:40:35 GMT

Pls help me

Re: Insurance Claims

sasikanth — Wed, 22 Feb 2017 11:22:10 GMT

HI,

As Pemium Amount is same for policy No try to find out MInDate, MaxDate for a policy.

Just try below script , PFA for reference

premium:
LOAD min(From_date) AS From_date, Max(To_date) as To_date, Policy_No,Premium Group by  Policy_No,Premium;
LOAD Policy_No, 
     From_date,
     To_date,
     Premium
FROM
premium.xls
(biff, embedded labels, table is Sheet2$);


CLAIMS:
LOAD Policy_No,
     claim_No,
     LOSS_DATE,
     CLAIMS
FROM
CLAIMS.xlsx
(ooxml, embedded labels, table is Sheet1);


Inner Join
IntervalMatch (Policy_No,LOSS_DATE)
LOAD From_date, To_date
Resident premium;

I hope it will work else please do let us know

Re: Insurance Claims

upaliwije — Wed, 22 Feb 2017 12:40:10 GMT

In fact my output report should like given below

Policy_No	From_date	To_date	Premium	claim No	loss date	calims
1	1-Jan-14	31-Dec-14	2000	C1	1-Jun-14	1000
				C2	1-Nov-14	500
1	1-Jan-15	31-Dec-15	2000	C6	1-Jun-15	200
2	1-Jan-14	31-Dec-14	5000	C3	1-Jul-14	100
2	1-Jan-15	31-Dec-15	5000	C7	1-Jan-15	500
				C8	1-Sep-15	700
2	1-Jan-16	31-Dec-16	5000	C10	1-Mar-16	800
				C12	5-Mar-16	300

Re: Insurance Claims

sasikanth — Wed, 22 Feb 2017 12:59:50 GMT

Try below script then , also attached application PFA

premium:
LOAD Policy_No,  
     From_date, 
     To_date, 
     Premium
FROM
premium.xls
(biff, embedded labels, table is Sheet2$);

Left Join

CLAIMS:
LOAD Policy_No, 
     claim_No, 
     LOSS_DATE, 
     CLAIMS
FROM
CLAIMS.xlsx
(ooxml, embedded labels, table is Sheet1);

Final:
LOAD * Where Flag='Y';
LOAD *,
IF(LOSS_DATE>From_date and LOSS_DATE<To_date,'Y','N') as Flag Resident premium ;
DROP Table premium;