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

    Combining 2 tables

    Anusha Vijayakumar

      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
          Martin Mahler

          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
              Anusha Vijayakumar

              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: Combining 2 tables
              Steve Dark

              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