Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
do you have movement type for every claim in claim table
and notification date also available in claim data
Hello Upali,
Trust that you are doing good!
As per my primary understanding you can make use of IntervalMatch functionality (refer below given link).
It would be helpful if you could share the application with sample data.
Regards!
Rahul
Hi Rahul
Thanks for your reply.
I attach h/w my sample data kindly advise me
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
Thanks,
I have an issue. What you can see from the attachment in My pivot table Premium is duplicated . How can I avoid it
HI,
Try to use this
Inner Join
IntervalMatch (LOSS_DATE)
LOAD From_date, To_date
Resident premium;
PFA,
I want to show premium only per policy period although more than one claim has occurred for the particular;la policy
Pls help me
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