Interval match with both date and time

Thu, 11 Jun 2015 07:14:14 GMT

Hey everyone,

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:

[From_date],

[From_Time],

[To_date],

[To_Time]

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 ============

Tmp_TRANS_BridgeTable:

IntervalMatch (SubIntervalBegin, EXTERNAL_ID)

Load distinct FROM_DATE, TO_DATE, TmpEXTERNAL_ID as EXTERNAL_ID resident TRANS;

TRANS_BridgeTable:

Load

EXTERNAL_ID & '|' & FROM_DATE & '|' & TO_DATE as TRANS_ID,

EXTERNAL_ID & '|' & SubIntervalBegin as SubIntervalID

Resident Tmp_TRANS_BridgeTable;

I now have separated the subintervals table to have instead of a [SubIntervalBegin] column, have 2 columns for:

[SubIntervalBeginDate], [SubIntervalBeginTime]

I'm not sure how to update my script.

Thanks beforehand

topic Interval match with both date and time in QlikView

Interval match with both date and time