Master Rating: Using Tables and Row Lookup Loops

From SchemeServe Help
Jump to: navigation, search

Introduction

This area of SchemeServe is more complex than most others. In order for you to make the most out of using our Help Guide for developing your own rates, it would be best if you read all the parts of this guide in order. To help you with this they are listed in numerical order in the “See Also” section of each page. You can click on a link to take you to section 1 and then move through logically at your own pace.

Using Tables and Row Lookup Loops

Prerequisites for using this master class:

  • You must build a table into one of your question sets.
  • You must have at least one Row Charge in your table.

Tables are often used to rate a potentially unknown number of items, such as when the user inputting the quote is entering a list of insurables.


An example would be say, specific areas of surgery a surgeon operates within or types of work a builder wants cover for.

In the example below we will assume:

  1. You have a Table called "CoveredAreas", with two columns "AreaOfWork" and "PercentageOfTurnover"; and a charge called "AreaCharge".
  2. A question called "Turnover"
  3. A question called "Postcode"
  4. A File Lookup called "Postcode Banding" with Columns: "Postcode" and "RatingArea"
  5. A File Lookup called "Rating File Lookup" with
    1. "Area" being the areas you can cover
    2. "StdRate" being the rate to charge for that area of work and
    3. "HighRate" being an increased rate based on the result of the "Postcode Banding" Lookup.


You need to rate each area of work based on the Percentage Of Turnover it accounts for.

COL 1: Get the amount of turnover that the particular PercentageOfTurnover equates to.
COL 2: Get the Postcode Lookup Result
COL 3: If the result from the Postcode Lookup is "High" use the "HighRate" column from "Rating File Lookup", otherwise use the "StdRate" column.


To do this we'll use the function SetRateFromLookup with our table. When using tables, the systax of SetRateFromLookup is as follows:

ROW1: SetRateFromLookup:TABLESystemId_COLUMNSystemId_TODOLOOKUPWITH|Lookup File Name
ROW2: TABLESystemId_CHARGERowSystemId|COLUMNName_INLOOKUP

NOTE: Notice how all Column SystemIds and Row Charge SystemIds must be prefixed with the SystemId of the table they belong to (followed by an underscore, i.e. TableId_ColumnOrChargeId.

So our file needs to look like this:

COL 1COL 2COL 3
1.SetCover:CoveredAreas_AreaChargeAnswerLookup:Postcode|Postcode BandingSetRateFromLookup:CoveredAreas_AreaOfWork|Rating File Lookup
2.[Turnover]*[CoveredAreas_PercentageOfTurnover] / 100NormalCoveredAreas_AreaCharge|StdRate
3.[Turnover]*[CoveredAreas_PercentageOfTurnover] / 100HighCoveredAreas_AreaCharge|HighRate


Rating based on the highest rate from a Table Question

If your underwriters are feeling a little frugal you can always use the highest rate from the given areas:

To keep it broken down by AreaOfWork, use Example 3 above and just replace "SetRateFromLookup:" with "SetHighestRateFromLookup:"

That way you will have an individual charge for each area.

If you just want to set the rate on the Turnover question then you can omit the PercentageOfTurnover column if you wish and:

1.AnswerLookup:Postcode|Postcode BandingSetHighestRateFromLookup:CoveredAreas_AreaOfWork|Rating File Lookup
2.NormalTurnover|StdRate
3.HighTurnover|HighRate


See Also:

  1. Introduction to Rates in SchemeServe
  2. Uploading SchemeServe Rating Files
  3. Downloading SchemeServe Rating Files
  4. Flat Rating: Building Your First SchemeServe Rating File
  5. Conditions for Rating Files
  6. Actions for Rating Files
  7. Rating Modifiers: What they are and when to use them
  8. Multi-Step Ratings: Using Multiple Sections in Rating Files
  9. Advanced Ratings: Using Formulae
  10. Advanced Ratings: Conditional Statements and Functions
  11. Advanced Ratings: Rounding Values
  12. Master Rating: Using Tables and Row Lookup Loops
  13. Example Ratings for Specific Insurance Types (Life and Public Liability)
  14. Mathematical Helper Functions for Ratings Files
  15. Rating Agents: Tips and Tricks