Insurance Claims

[upaliwije]

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.

[ramachandra_gee]

do you have movement type for every claim in claim table

and notification date also available in claim data

[rahulpawarb]

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/Interva...

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

Regards!

Rahul

[upaliwije]

Hi Rahul

Thanks for your reply.

I  attach h/w my sample data kindly advise me

[rahulpawarb]

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

[upaliwije]

Thanks,

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

[sasikanth]

HI,

Try to use this

Inner Join

IntervalMatch (LOSS_DATE)

LOAD From_date, To_date

Resident premium;

PFA,

[upaliwije]

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

[upaliwije]

Pls help me

[sasikanth]

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