Example: Formula
Applying Formulas and SQL Functions to Characteristics
In the example, we apply a simple SQL formula to compare two fields containing dates and we mark a third field according to that formula.
Input Data
| Contract ID | Date_1 | Date_2 | Total Premium |
|---|---|---|---|
| A | 2019-01-01 | 2019-02-01 | 300 |
| B | 2019-04-01 | 2019-03-01 | 400 |
Flow Modeling Configuration
| Rule | Description | Rule Type | State | Rule Grouping | Rule Ordering | Selection |
|---|---|---|---|---|---|---|
| CHARF | Characteristic Formula | Characteristic Formula | Active |
| Line | Formula | Result |
|---|---|---|
| L001 |
CASE WHEN DATE_1 > DATE 2 THEN 'X' ELSE " END |
DATE_IND |
When DATE_1 is greater than DATE_2, the field DATE_IND (Date Indicator) is marked with “X”.
Key Configuration Description
The table below explains the meaning of the options required to run this configuration.
| Field | Meaning | Notes |
|---|---|---|
| Line | Identifier of the line | Must be unique. A single characteristic formula rule can run multiple lines. |
| Formula | SQL formula that has to be applied to categorical variables | Mandatory input |
| Result | Field where the result of the rule is stored |
Mandatory output. Must be defined in the Action section of the Signature tag. |
Expected Result
| Contract ID | Date_1 | Date_2 | Total Premium | Date Indicator |
|---|---|---|---|---|
| A | 2019-01-01 | 2019-02-01 | 300 | |
| B | 2019-04-01 | 2019-03-01 | 400 | X |
Applying Formulas and SQL Functions to Key Figures
In this example, we apply a very simple SQL formula to compute the weighted premium (WEIGHTED_PREMIUM) based on the fields TOTAL_PREMIUM and WEIGHT.
Input Data
| Contract ID | Date_1 | Total Premium | Weight |
|---|---|---|---|
| A | 2019-01-01 | 300 | 0.4 |
| B | 2019-04-01 | 400 | 0.6 |
Flow Modeling Configuration
| Rule | Description | Rule Type | State | Rule Grouping | Rule Ordering | Selection |
|---|---|---|---|---|---|---|
| KFF | Key Figure Formula | Formula | Active |
| Line | Formula | Result |
|---|---|---|
| L001 | TOTAL_PREMIUM*WEIGHT | WEIGHTED_PREMIUM |
Key Configuration Description
The table below explains the meaning of the options required to run this configuration.
| Field | Meaning | Notes |
|---|---|---|
| Line | Identifier of the line | Must be unique. A single Key Figure Formula rule can run multiple lines. |
| Formula | SQL formula that has to be applied to numerical variable | Mandatory input |
| Result | Field where the result of the rule is stored | Mandatory output. Must be defined in the Action section of the Signature tag. |
Expected Output
| Contract ID | Date_1 | Total Premium | Weight | Weighted Premium |
|---|---|---|---|---|
| A | 2019-01-01 | 300 | 0.4 | 120 |
| B | 2019-04-01 | 400 | 0.6 | 240 |