8 Replies Latest reply: Feb 18, 2014 3:40 AM by Steve Dark

# Combining 2 tables

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

• ###### Re: Combining 2 tables

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?

• ###### Re: Re: Combining 2 tables

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.

• ###### Re: Re: Re: Combining 2 tables

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.

• ###### Re: Combining 2 tables

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

• ###### Re: Combining 2 tables

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.

• ###### Re: Combining 2 tables

Hi, I sent you message...

• ###### Re: Combining 2 tables

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.

• ###### Re: Combining 2 tables

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