Combining 2 tables

[#1]

Hi all,

I work for a Insurance firm where we need to find the Net Incurred losses.

Net Incurred Losses=Claim Amt- Deductible.

But the deductible has 3 scenarios,

1. Deductible per claim

2  Aggregate deductible per policy

3. Deductible per claim and deductible aggregate at the policy level

Lets say I have 2 tables Claims and Deductible. There are no flags to indicate the deductible per claim or aggregate or both.

The tables are connected by policy number.

Deductible per claim.

If the DedPerClaim field is not 0 and DedAggAmt field is 0, then Incurred Loss= Claim Amt- DedPerClaim

Deductible Aggr

If the DedPerClaim field is  0 and DedAggAmt field is not 0, the company will pay any amount after the total claim amount of that policy reaches to the DedAggAmt

3rd is the complicated scenario of Per claim and Aggregate at the policy level

Attached an excel. Can someone please help me on how to script this and show the values in straight table.

Thanks

[simondachstr]

Can you clarify the following sentence:

"the company will pay any amount after the total claim amount of that policy reaches to the DedAggAmt"

From my understanding you have 2 tables (Claims & Deducitble) and you want to model table 3 in the QV script?

[stevedark]

Typically the best way to approach these scenarios is to concatenate the tables, giving any common fields exactly the same name.  This way you can aggregate values for Claims and Premiums across various dimensions, such as Policy Number, and do the kinds of calculations you are describing.

Hope that helps,

Steve

[#4]

Thanks for the replies. A policy can have number of claims. The claimant needs to pay until all of their claims exceed the amount that is stored in the DedAggAmt field. Attaching a test qvw . I added a table that lists all the details and then a chart that implements the Dedperclaim.

But I need to do the grouping for the Policies# 2 and 3 to get the correct value for the Incurred Losses column.

The total claim value for the policy# 2 is 2800 but the Deductible aggregate amount is 3000, meaning the claimant has to pay the first 3000. So the losses will be 0 in this case.

In the 3rd scenario, there is both per claim and aggregate, so 3A and 3B claims will be paid 1500 and 500 and 3C, 3D will be 0 as they did not exceed 500. The total claim value is 3500 so far. But lets say next we receive more claims for the policy 3 and the claim value reaches 5000 then we need to pay out all the claims.

[albertovarela · Accepted solution]

Hello,

I played around with you data (actually added some records to tested the different scenarios) and came up with the attached qvw.

I hope you find it useful.

[#6]

Thanks a bunch. This is what I was looking for. I will implement this script into my data model. Will let you know if I have any questions

[#7]

Hi Alberto,

I tried to add this into my model and I cannot get it work (script takes longer to load than usual and I see duplicates in claims) because of other fact tables and calculations involved. Not sure how to troubleshoot and I am fairly new to QV.

Thank you. Your help is much appreciated.

[albertovarela]

Hi, I sent you message...

[stevedark]

You need to be really careful when using JOINs in load scripts as if there is a one to many join then rows (and values) will be duplicated.  A concatenate removes this risk.  It should be possible to then do the calculations you require in the front end - but I have not tried this out.