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.

[upaliwije]

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

[sasikanth · Accepted solution]

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;