I'm fairly new to Qlik and I'm trying to build up my data model.
My data model is made mainly with slowly changing dimensions, which I have combined into one 'Sub intervals' table (Based on the partitioning solution in IntervalMatch and Slowly Changing Dimensions).
Basically my end result is being able to choose a point in time, and see the state of my members based on the different slowly changing dimensions.
I was able to get this to work, however it was quite heavy and the solution affected performance even when I only had about 5% of the data load I'll have eventually...
I figured a big cause for this was the fact that I kept all my dates in a timestamp format of 'yyyy-MM-dd hh:mm:ss' meaning that had a lot of different entries on the calendar table for the same date ( which caused problems in normalizing and compacting the date).
So I decided to split my periods from a column for "From_datetime" and a column for "To_datetime", to 4 columns:
However I'm stuck on the step where I do the interval match between my sub intervals and the original slowly changing dimensions:
This is the code I had which worked, before the separation of date and time. Note that EXTERNAL_ID is a unique persons ID which is linked to each period.
|// ============ Create the bridge table between the subintervals and the TRANS ============|
IntervalMatch (SubIntervalBegin, EXTERNAL_ID)
Load distinct FROM_DATE, TO_DATE, TmpEXTERNAL_ID as EXTERNAL_ID resident TRANS;
|EXTERNAL_ID & '|' & FROM_DATE & '|' & TO_DATE as TRANS_ID,|
EXTERNAL_ID & '|' & SubIntervalBegin as SubIntervalID
I now have separated the subintervals table to have instead of a [SubIntervalBegin] column, have 2 columns for:
I'm not sure how to update my script.