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

[Anil_Babu_Samineni]

Just replace same with Policy Date field for Flag equation

[nevilledhamsiri]

Dear Anil,

This is the error I get when loading data after appyling the same in to policy data

error in expression:

Date takes 1-2 parameters

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,

     If(Date(Date#(If(Len(POL_PERIOD_FROM)>0, Date(POL_PERIOD_FROM)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY'), Dual('2015 Dates',1),

     If(Date(Date#(If(Len(POL_PERIOD_FROM)>0, Date(POL_PERIOD_FROM), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2016)),'DD/MM/YYYY'), Dual('2016 Dates', 2),

     If(Date(Date#(If(Len(POL_PERIOD_FROM)>0, Date(POL_PERIOD_FROM), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2017)),'DD/MM/YYYY'), Dual('2017 Dates',3), Dual('Null',4)))) as Flag1

FROM (ooxml, embedded labels, table is Sheet1)

This is the script written!

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,

     If(Date(Date#(If(Len(POL_PERIOD_FROM)>0, Date(POL_PERIOD_FROM)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY'), Dual('2015 Dates',1),

     If(Date(Date#(If(Len(POL_PERIOD_FROM)>0, Date(POL_PERIOD_FROM), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2016)),'DD/MM/YYYY'), Dual('2016 Dates', 2),

     If(Date(Date#(If(Len(POL_PERIOD_FROM)>0, Date(POL_PERIOD_FROM), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2017)),'DD/MM/YYYY'), Dual('2017 Dates',3), Dual('Null',4)))) as Flag1

FROM (ooxml, embedded labels, table is Sheet1) ;

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,

     If(Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY'), Dual('2015 Dates',1),

     If(Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2016)),'DD/MM/YYYY'), Dual('2016 Dates', 2),

     If(Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2017)),'DD/MM/YYYY'), Dual('2017 Dates',3), Dual('Null',4)))) as Flag

FROM (ooxml, embedded labels, table is Sheet1)   ;

Please see where I have gone wrong!

Thanks Mr Anil

[Anil_Babu_Samineni]

Try this?

If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2010)),'DD-MMM-YYYY'), Dual('2010 Dates',1),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2011)),'DD-MMM-YYYY'), Dual('2011 Dates',2),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2012)),'DD-MMM-YYYY'), Dual('2012 Dates',3),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2013)),'DD-MMM-YYYY'), Dual('2013 Dates',4),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2014)),'DD-MMM-YYYY'), Dual('2014 Dates',5),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2015)),'DD-MMM-YYYY'), Dual('2015 Dates',6),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2016)),'DD-MMM-YYYY'), Dual('2016 Dates',7),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2017)),'DD-MMM-YYYY'), Dual('2017 Dates',8))))))))) as Flag1

[nevilledhamsiri]

Dear Anil,

Now the correct period figures could be taken. But the total figures coming looks wrong. Eg total premium per year needs to be over 100 millions.

Can you please see why it is & correct figures wont come?

Regds

Neville

OLICIES:

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,

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2010)),'DD-MMM-YYYY'), Dual('2010 Dates',1),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2011)),'DD-MMM-YYYY'), Dual('2011 Dates',2),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2012)),'DD-MMM-YYYY'), Dual('2012 Dates',3),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2013)),'DD-MMM-YYYY'), Dual('2013 Dates',4),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2014)),'DD-MMM-YYYY'), Dual('2014 Dates',5),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2015)),'DD-MMM-YYYY'), Dual('2015 Dates',6),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2016)),'DD-MMM-YYYY'), Dual('2016 Dates',7),

     If(Date(Date#(POL_PERIOD_FROM, 'DD-MMM-YY'),'DD-MMM-YYYY')<Date(YearEnd(MakeDate(2017)),'DD-MMM-YYYY'), Dual('2017 Dates',8))))))))) as Flag1

FROM (ooxml, embedded labels, table is Sheet1) ;

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,

     If(Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2015)),'DD/MM/YYYY'), Dual('2015 Dates',1),

     If(Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2016)),'DD/MM/YYYY'), Dual('2016 Dates', 2),

     If(Date(Date#(If(Len(PAID_DATE)>0, Date(PAID_DATE)), 'MM/DD/YYYY'),'DD/MM/YYYY')<Date(YearEnd(MakeDate(2017)),'DD/MM/YYYY'), Dual('2017 Dates',3), Dual('Null',4)))) as Flag

FROM (ooxml, embedded labels, table is Sheet1)   ;

[Anil_Babu_Samineni · Accepted solution]

If you look Data model from CLAIMS table "Subset ration" is very Bad. If you want to full full them you could manipulate following Generating Missing Data In QlikView or else why Subset ration not full filled information density and subset ratio? What is the use of this?‌ So, You need to understand the Data model how this going to work. But, From my testing it returns as expected. I'm done some testing in excel along with Qlik. It's returning as perfect.. Not sure what you meant here?

To get Information Density 100% in Data model you can simply call them using Where Exists(Primary Key) for CLAIMS table.

[nevilledhamsiri]

Thanks a lot Anil

Hope you may help in my future issues as well.

Regds

Neville

[Anil_Babu_Samineni]

Sure, Glad it got worked.