Where the same risks or Claims are split across multiple rows in a bordereau, Tide offers the option to aggregate these into a single row. In Step 3 of the Questionnaire, for all bordereau types, selecting ‘Yes’ to the question ‘Do Risk Entries split across multiple rows need to be combined into a single entry?’ will cause Tide to display a button named ‘Specify Aggregation’.
Clicking the Specify Aggregation button will open the Specify Aggregation modal. This modal contains a grid with three columns: Bordereau Field, Function and Method.
Bordereau Field
This column contains a list of all columns of data as they appear in the bordereau sheet selected for mapping in the Questionnaire.
Function
This is where you specify how the row of data in the bordereau should be understood by Tide. It contains a maximum of two options:
- Aggregate – This is the default selection for each column heading in the bordereaux when the Specify Aggregation modal opens. This Function instructs Tide to Aggregate the data in this column based on the Method selected.
- Coverage – This option is only available for Risk and Risk Premium Bordereau Types. This Function is to be used where Coverage information has not been split out by coverage in the bordereau (e.g. a single column named ‘Coverage Name’ containing on separate rows the names of multiple coverages bound in a single policy). This Function instructs Tide to transpose each of the unique values in that column into their own column.
Method
Selected Function: Aggregate
- Group By/Sum – This is the default selection for all Bordereau fields in the Specify Aggregation modal.
Where the bordereau field is a text value (e.g. Policy Reference), all unique groups of the text value will be aggregated into a single row.
Where the bordereau field is a numeric value (e.g. Fees), all rows Grouped By a text field (e.g. Policy Reference) will have their numeric values in this column (in this example Fees) Summed together.
- First/ Min - Where the bordereau field is a text value (e.g. Policy Reference), Tide will select the first value in each unique group.
Where the bordereau field is a numeric value (e.g. Fees), all rows Grouped By a text field (e.g. Policy Reference) will have their minimum value selected in this column (in this example Fees).
- Last/Max - Where the bordereau field is a text value (e.g. Policy Reference), all unique groups of the text value will be aggregated into a single row.
Where the bordereau field is a numeric value (e.g. Fees), all rows Grouped By a text field (e.g. Policy Reference) will have their numeric values in this column (in this example Fees) Summed together.
Selected Function: Coverage (only available in Risk and Risk Premium questionnaires)
Each of the Tide Field options for Coverage mapping are offered here:
- Coverage Currency
- Coverage Deductible or Excess Amount
- Coverage Deductible or Excess Basis
- Coverage Deductible or Excess Percentage
- Coverage Deductible or Excess Type
- Coverage Gross Premium
- Coverage inclusion flag (Y/N)
- Coverage Limit
- Coverage Name
- Coverage Total Sums Insured
Only a single field Bordereau field can be selected for each Tide Coverage field.
Where Function has been set to Coverage, Tide will enforce Coverage Name to be set against one of the Bordereau Fields listed.
Where the Specify Aggregation modal has been saved with Function set to Coverage for a number of the Bordereau fields, Step 10 (Coverages) of the Questionnaire will contain a placeholder in each of the Coverage field in the grid. The placeholder will state that Coverages have been mapped in Step 3 of the Questionnaire and the respective Bordereau Field.
Returning to the Step 3 Specify Aggregation once the Bordereau has been mapped and attempting to change the Function of a particular Bordereau field will not be possible if that field has already been mapped.
Transformation
Where Coverage Name has been selected as a Method in the Specify Aggregation modal, Tide will present these during Transformation for cleansing to Tide Coverage values.