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)
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.
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.
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.
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!
What is your goal? In UI / Script?
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.
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.
Could be better approach, If you provide sample output needed?
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
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)