0 Replies Latest reply: Jun 11, 2015 3:14 AM by Daniel Balchasan RSS

    Interval match with both date and time

    Daniel Balchasan

      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