I'm fairly new to Qlik, and I'm working on designing my data model. I've been trying to set up a model where I have a period and I'm linking this period to a master calendar table.
While I tried doing this with periods with an end date, it worked fine. However when I included open ended periods in my data set, I came across an issue. My code for creating the bridge table between the Transfers table (trans) and the Master Calendar table
// ============ Create the Transfers_Calendar ============
FROM_DATE as Trans_DATE,
Month(FROM_DATE) as Trans_MONTH;
Date(MinDate+iterno()) as FROM_DATE
While iterno() <= MaxDate - MinDate ;
Min(FROM_DATE)-1 as MinDate,
max(TO_DATE) as MaxDate
// ============ Create the bridge table between the Transfers and the Transfers_Calendar ============
left(Date( FROM_DATE + IterNo() - 1 ),10) as Trans_DATE
While IterNo() <= TO_DATE - FROM_DATE + 1 ;
It's pretty obvious that it wont create a link when the end date is a null.
I've tried looking into a solution where I create a fictional end date (like 2099-12-31), but that increases the size of my data by a very large factor.
This is the most common scenario for me, as I use the periods to show changes in a 'Status' of a client (Active, Inactive, Frozen) and most of the periods don't have an end date.
I wanted to ask what is the best practice for handling open periods?
I don't understand what do you want to do with the bridge-table. Normally it wouldn't be needed to link a fact-table to a master-calendar. For many different date-fields you could use these approach: Canonical Date.
My data model contains a slowly changing dimension called "Transfers". It shows the periods where a member belongs to a certain "Home Club" (Numerical value). It's a table with: From_Date, To_Date , Person_ID, Home_Club_ID.
What I'm trying to do is to be able to choose a point in time (a day), and be able to count how many people are listed at each "Home Club" at that point in time.
I thought about taking a value which is relative to 'Today' as the end date (E.g today + 5 years), but I was concerned about the efficiency of having to update all (or most of) my periods every day. I was hoping to have a setup where I only import new data and 'Changes' to existing entries, instead of reloading my whole data set on a daily basis.
I was thinking this is a strange way to approach this. Eventually I will end up with a table that has an entry for each 'person' and each day since he started. So basically the size is based on people multiplied by days. When I have 2 million people over a period of 10 years, it sounds like a whole lot of data, that should be optimized somehow.
I know that Qlik has some good methods to normalize and compact data. Do you know how feasible it is to work with a data set like that?