Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i am facing the following challange: I have a table that contains contract data. Every contract can have multiple periods, i.e.:
Contract ID | Valid Form | Valid To |
123 | 2023-01-01 | 2023-12-31 |
123 | 2024-01-01 | 2024-12-31 |
There are several other tables in the source system's database that contain the contract conditions. All records are structured the same way, i.e.:
Conditition ID | Contract ID | Valid From | Valid To | Condition | Type |
ABC | 123 | 2023-01-01 | 2023-07-13 | 120.000 | Premium |
ABC | 123 | 2023-07-14 | 2024-12-31 | 500.000 | Premium |
The business requirement here is to be able to answer questions such as "What is my premium for a given contract and month(-year) ?" In order to answer a question like this, i figure, i need first break down my contract records into one record per month (did that; works) and then (not sure how to approach this) i need to connect the conditions to the monthly records - but how ? (especially when you can have two conditions being valid in one month) ...
Any help is much appreciated. Thanks in advance!
Hmm. I think you could this with 2 methods. You could connect the table trough interval match. Or you could create both tables with a monthly record and create a key with ContractID and Month.
But what do you want to do with the condition?
To answer questions like "What is my premium for a given contract and month-year?
By providing a structure where each month is linked to the relevant conditions, even if there are multiple conditions in a single month.
For a specific month and year, use set analysis to filter the data accordingly. For example, to find the premium for contract 123 in March 2023:
SUM({<ContractID={'123'}, Month={'Mar'}, Year={'2023'}>} Premium)
you can use a combination of Qlik's data modeling capabilities and set analysis.
// Assuming 'Contracts' is your monthly broken down contracts table
// and 'Conditions' is your conditions table
// Master Calendar
Calendar:
LOAD Date(MinDate + IterNo() - 1) as Date
AUTOGENERATE 1 WHILE MinDate + IterNo() - 1 <= MaxDate;
// IntervalMatch
IntervalMatch (Date)
LOAD DISTINCT Valid From, Valid To
RESIDENT Contracts;
// Join the conditions to the matched intervals
LEFT JOIN (Calendar)
LOAD ConditionID, ContractID, Condition, Type
RESIDENT Conditions;
// Now your Calendar table has fields for both contracts and conditions
Above script is only for sample you can create as per your request.
*** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. ***