Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

How to restrict data for different periods

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

26 Replies
Anil_Babu_Samineni

Just replace same with Policy Date field for Flag equation

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nevilledhamsiri
Specialist
Specialist
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nevilledhamsiri
Specialist
Specialist
Author

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

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.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
nevilledhamsiri
Specialist
Specialist
Author

Thanks a lot Anil

Hope you may help in my future issues as well.

Regds

Neville

Anil_Babu_Samineni

Sure, Glad it got worked.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)