topic Re: How to restrict data for different periods in QlikView

How to restrict data for different periods

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to restrict data for different periods

nevilledhamsiri — Thu, 28 Dec 2017 17:02:49 GMT

Hi, All

Appreciate if somebody help me on this. As suggested, I need to split the data for three periods as well as to avoid repetition of same premium.

Rgds

Neville

Re: How to restrict data for different periods

nevilledhamsiri — Fri, 29 Dec 2017 17:43:00 GMT

Kindly look in to this. I have some data to be presented in the suggested manner. Hope some one will dearly look in to this!

Thanks

Neville

Re: How to restrict data for different periods

Anil_Babu_Samineni — Sat, 30 Dec 2017 02:18:32 GMT

What is your goal? In UI / Script?

Re: How to restrict data for different periods

nevilledhamsiri — Sun, 31 Dec 2017 17:20:41 GMT

Dear Anil,

I have restricted the policy files through where clause there by just by changing the date given I can arrange the data for the respective period but same if applied on claim file, expected out put wont come. Please see how I could do this?. Thank you for responding on this.

Regds

Neville

Re: How to restrict data for different periods

nevilledhamsiri — Sun, 31 Dec 2017 17:25:15 GMT

Also Anil please see how I could resolve the following issue in addition to the periods where relevant data to be arranged

"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.

Neville

Re: How to restrict data for different periods

nevilledhamsiri — Sun, 31 Dec 2017 18:05:09 GMT

If you could suggest the answers both in UI & Script that would be fine.

Thanks

Neville

Re: How to restrict data for different periods

Anil_Babu_Samineni — Tue, 02 Jan 2018 12:36:14 GMT

Could be better approach, If you provide sample output needed?

Re: How to restrict data for different periods

nevilledhamsiri — Tue, 02 Jan 2018 17:54:36 GMT

Dear Anil,

Actually I need two out puts to be achieved!

A) To separate policy & claims data for respective three periods. In this process I may be able to changed the given end date such as 2015,12,31, 2016,12,31,2017,12,31 to view data for separate three periods. But the issue is the same where clause if applied on claim data, the figures produced are not correct. Please see the reason for that. But it works well on policy data.

B) When more claims are paid on one policy, the respective premium is repeated against each claim . Please see the P/T below where on RA0009TC002069 has 10 claims & the single premium of Rs 456305/= is repeated 10times. How this could be restricted to just a one time presentation.

For the issue A- I have annexed the script with where clause

Thanks

Neville

Re: How to restrict data for different periods

Anil_Babu_Samineni — Wed, 03 Jan 2018 02:34:26 GMT

May be

A) For policy, You can make like below

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

B) It is because CLAIM_NO also existing in Dimension. To get rid you can use Aggr(SUM(GROSS), POLICY_NO)

Re: How to restrict data for different periods

nevilledhamsiri — Thu, 04 Jan 2018 17:58:35 GMT

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') ;

Re: How to restrict data for different periods

nevilledhamsiri — Fri, 05 Jan 2018 16:56:06 GMT

Hi Anil,

Will you look in to this if you are free?

Neville

Re: How to restrict data for different periods

Anil_Babu_Samineni — Fri, 05 Jan 2018 17:10:03 GMT

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');

Re: How to restrict data for different periods

nevilledhamsiri — Fri, 05 Jan 2018 18:39:54 GMT

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

Re: How to restrict data for different periods

Anil_Babu_Samineni — Sat, 06 Jan 2018 01:55:23 GMT

PFA

Re: How to restrict data for different periods

nevilledhamsiri — Sat, 06 Jan 2018 15:04:01 GMT

Dear Anil,

What does PFA Stand for? No idea Please help

Re: How to restrict data for different periods

Anil_Babu_Samineni — Sat, 06 Jan 2018 15:08:38 GMT

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

PFA - Please Find Attachment

Re: How to restrict data for different periods

nevilledhamsiri — Sat, 06 Jan 2018 15:12:17 GMT

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

Re: How to restrict data for different periods

Anil_Babu_Samineni — Sat, 06 Jan 2018 15:15:30 GMT

Try to reply over conversation it self rather yours to repeat

PFA

Re: How to restrict data for different periods

nevilledhamsiri — Sat, 06 Jan 2018 15:54:28 GMT

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