topic Re: Data Modelling Question in App Development

Data Modelling Question

xyz_1011 — Tue, 21 May 2024 07:00:35 GMT

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!

Re: Data Modelling Question

JHuis — Tue, 21 May 2024 07:30:58 GMT

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?

Re: Data Modelling Question

TauseefKhan — Tue, 21 May 2024 08:08:20 GMT

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. ***