Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
albertovarela
Partner - Specialist
Partner - Specialist

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.

View solution in original post

8 Replies
simondachstr
Luminary Alumni
Luminary Alumni

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

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

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

Hi, I sent you message...

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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.