Master Rating: Using Tables and Row Lookup Loops
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:
- You have a Table called "CoveredAreas", with two columns "AreaOfWork" and "PercentageOfTurnover"; and a charge called "AreaCharge".
- A question called "Turnover"
- A question called "Postcode"
- A File Lookup called "Postcode Banding" with Columns: "Postcode" and "RatingArea"
- A File Lookup called "Rating File Lookup" with
- "Area" being the areas you can cover
- "StdRate" being the rate to charge for that area of work and
- "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 1||COL 2||COL 3|
|1.||SetCover:CoveredAreas_AreaCharge||AnswerLookup:Postcode|Postcode Banding||SetRateFromLookup:CoveredAreas_AreaOfWork|Rating File Lookup|
|2.||[Turnover]*[CoveredAreas_PercentageOfTurnover] / 100||Normal||CoveredAreas_AreaCharge|StdRate|
|3.||[Turnover]*[CoveredAreas_PercentageOfTurnover] / 100||High||CoveredAreas_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 Banding||SetHighestRateFromLookup:CoveredAreas_AreaOfWork|Rating File Lookup|
- Introduction to Rates in SchemeServe
- Uploading SchemeServe Rating Files
- Downloading SchemeServe Rating Files
- Flat Rating: Building Your First SchemeServe Rating File
- Conditions for Rating Files
- Actions for Rating Files
- Rating Modifiers: What they are and when to use them
- Multi-Step Ratings: Using Multiple Sections in Rating Files
- Advanced Ratings: Using Formulae
- Advanced Ratings: Conditional Statements and Functions
- Advanced Ratings: Rounding Values
- Master Rating: Using Tables and Row Lookup Loops
- Example Ratings for Specific Insurance Types (Life and Public Liability)
- Mathematical Helper Functions for Ratings Files
- Rating Agents: Tips and Tricks