Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
upaliwije
Creator II
Creator II

Insurance Claims

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.

1 Solution

Accepted Solutions
sasikanth
Specialist III
Specialist III

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;

View solution in original post

11 Replies
ramachandra_gee
Contributor III
Contributor III

do you have movement type for every claim in claim table

and notification date also available in claim data

rahulpawarb
Specialist III
Specialist III

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
Creator II
Creator II
Author

Hi Rahul

Thanks for your reply.

I  attach h/w my sample data kindly advise me

rahulpawarb
Specialist III
Specialist III

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
Creator II
Creator II
Author

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
Specialist III
Specialist III

HI,

Try to use this

Inner Join

IntervalMatch (LOSS_DATE)

LOAD From_date, To_date

Resident premium;


PFA,

upaliwije
Creator II
Creator II
Author

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

upaliwije
Creator II
Creator II
Author

Pls help me

sasikanth
Specialist III
Specialist III

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