How to restrict data for different periods

[nevilledhamsiri]

First let me wish you all a very happy christmas which is in the offing.

Dear Friends, (Dear S Khan, Tastiana  & all other dear friends)

Please help me to split the said data for following three periods (Premiums & Claims)

2015_Jan-Dec

2016_Jan_Dec

2017_Jan_Dec

1st file carries number of fields including insurance policies & the 2nd files carries the claim paid against such policies. Two files to be linked on policy number.

When more than one claim is paid against one insurance policy, same premium is repeated against each claim paid. This gives a wrong picture as per the premium. Therefore please also suggest the way  as to how such  a situation could be avoided.

Best Regards

Neville

[nevilledhamsiri]

Dear Anil, Sorry for delay in responding due to having out of couple of days. Your answer for B of course worked well. For A, where clause applied in Policy file worked fine. But if the same where clause applied on Claim data it seems an error occurs.It is the paid date in claim data that the restriction to be made. Though the pol_period from was replaced with paid date still it does not work. Will you show me the script where it should be made. Script written too presented below.

Regds

Neville

POLICIES:

LOAD CLA_DESCRIPTION, PRD_DESCRIPTION, TYPE, SHORT_NAME, POL_PERIOD_FROM ,  POL_PERIOD_TO,

     POL_TRN_DATE, POL_POLICY_NO AS POLICY_NO, POL_REF_NO, NAME, POL_SUM_INSURED, CP, RS, TC, GROSS, CESS, LIFE,

     VAT, NET, NO, T_CP, T_RS, T_TC, T_GROSS, T_CESS, T_LIFE, T_C, T_VAT, T_NET, T_SI, P_CP, P_RS,

     P_TC, P_GROSS, P_CESS, P_VAT, P_C, P_LIFE, P_NET, P_SI, C_CP, C_RS, C_TC, C_GROSS, C_CESS,

     C_VAT, C_LIFE, C_NET, C_C, C_SI, G_CP, G_RS, G_TC, G_GROSS, G_VAT, G_CESS, G_LIFE, G_NET, C,

     G_SI

FROM (ooxml, embedded labels, table is Sheet1) WHERE  Date(Date#( POL_PERIOD_FROM, 'DD-MMM-YY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY')  ;

CLAIMS:

LOAD RNUM, BRANCH, REPORTED_DATE, LOSS_DATE, CLASS_CODE, PRD_CODE, POLICY_NO  , NAME_OF_THE_INSURED,

     CLAIM_NO, RISK, BALANCE_BF, MONTHS_PROVISION, PAID_AMOUNT, UNDER_OVER_PROVISION, BALANCE_CF,

     PAID_DATE, CLOSED_DATE, CLOSED_STAUS, ADVANCED_PAYMENT, REMARKS, NO_OF_FINAL_PAYMENTS_IN,

     NO_OF_ADVANCED_PAYMENTS, PREV_MON_INT_PROV, REASON_OUTSTANDING, ME_CODE, CONV_CODE,

     CAUSE_OF_LOSS

FROM (ooxml, embedded labels, table is Sheet1) WHERE  Date(Date#( PAID_DATE, 'DD-MMM-YY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY')  ;

[nevilledhamsiri]

Hi Anil,

Will you look in to this if you are free?

Neville

[Anil_Babu_Samineni]

Try this?

POLICIES:

LOAD CLA_DESCRIPTION, PRD_DESCRIPTION, TYPE, SHORT_NAME, POL_PERIOD_FROM, POL_PERIOD_TO,

     POL_TRN_DATE, POL_POLICY_NO AS  POLICY_NO, POL_REF_NO, NAME, POL_SUM_INSURED, CP, RS, TC, GROSS, CESS, LIFE,

     VAT, NET, NO, T_CP, T_RS, T_TC, T_GROSS, T_CESS, T_LIFE, T_C, T_VAT, T_NET, T_SI, P_CP, P_RS,

     P_TC, P_GROSS, P_CESS, P_VAT, P_C, P_LIFE, P_NET, P_SI, C_CP, C_RS, C_TC, C_GROSS, C_CESS,

     C_VAT, C_LIFE, C_NET, C_C, C_SI, G_CP, G_RS, G_TC, G_GROSS, G_VAT, G_CESS, G_LIFE, G_NET, C,

     G_SI

FROM

[CUST_POLICY(2015_2017_DEC).xlsx] (ooxml, embedded labels, table is Sheet1)

WHERE  Date(Date#( POL_PERIOD_FROM, 'DD-MMM-YY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY');

CLAIMS:

LOAD RNUM, BRANCH, REPORTED_DATE, LOSS_DATE, CLASS_CODE, PRD_CODE, POLICY_NO, NAME_OF_THE_INSURED,

     CLAIM_NO, RISK, BALANCE_BF, MONTHS_PROVISION, PAID_AMOUNT, UNDER_OVER_PROVISION, BALANCE_CF,

     PAID_DATE, CLOSED_DATE, CLOSED_STAUS, ADVANCED_PAYMENT, REMARKS, NO_OF_FINAL_PAYMENTS_IN,

     NO_OF_ADVANCED_PAYMENTS, PREV_MON_INT_PROV, REASON_OUTSTANDING, ME_CODE, CONV_CODE,

     CAUSE_OF_LOSS

FROM

[[CLAIMS(2015_2017_DEC).xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY');

[nevilledhamsiri]

Thanks Anil for your response!

When I make 2015 in the script, as below, data both policies & claims looks like ok. But when mark 2016 as per 2nd table , not only 2016 data but even 2015 data comes like wise when mark 2017, all three years figures appearing. Since I need the data for that specific years such as ( 01.01.2015 to 31.12.2015), (01.01.2016 to 31.12.2016), (01.01.2017-31.12.2017), how I could achieve this. With out using a where clause if any easy solution can be suggested such as using a if statement in the script where a list box can be created so that on a click th required period can be chosen, that would be fine. Sorry for troubling, if possible please look in to this. Because in my office I am required to match premium against the claims for different period to find the profit/loss of the customers.

Regds

Neville

[Anil_Babu_Samineni]

PFA

[nevilledhamsiri]

Dear Anil,

What does PFA Stand for? No idea Please help

[Anil_Babu_Samineni]

First download the application and check whether requirement reached or not?

PFA - Please Find Attachment

[nevilledhamsiri]

Hi Anil,

I am having a personel edition which makes me no access to your attachment. Could you kindly send me the script in a note pad if you don't mind?

Regds

Neville

[Anil_Babu_Samineni]

Try to reply over conversation it self rather yours to repeat

PFA

[nevilledhamsiri]

Dear Anil,

Thank you very much for keep helping!

As you informed, now claims data can be separated over three Years. Similarly I need to do the same thing for the policies as well. Because if I am to find profit of 2015, I need to compare 2015 policies with 2015 claims & if I am to compare profits for 2016, policies & claims to be matched in that Year etc. Now I think almost reached the target please help me to do this for both policies & claims

Regds

Neville