IntervalMatch multiple tables

[jimbiggs]

I have a Fact table and Dimension table both with "from" and "to" dates.  I also have a calendar table with all of the dates.  If they select a date, I want to show the rows that are effective as of that point. I'm having trouble trying to get IntervalMatch to work against two tables, it always ends up with circular references.  I tried combining the dates into a single table, but no luck.

Any thoughts?

DIM:
NoConcatenate
LOAD * INLINE [
    Id, Dim_FromDt, Dim_ToDt, Dim_Value
    1, 1/1/2000, 1/1/2015, 10
    2, 1/1/2000, 7/1/2013, 15
    2, 7/2/2013, 1/1/2015, 16
    3, 1/1/2000, 1/1/2015, 5
];

FACT:
NoConcatenate
LOAD * INLINE [
    Id, Fact_FromDt, Fact_ToDt, Fact_Value
    1, 1/1/2000, 8/1/2013, 10
    1, 8/2/2013, 1/1/2015, 11   
    2, 1/1/2000, 1/1/2010, 15
    2, 1/2/2010, 1/1/2015, 16
    3, 1/1/2000, 1/1/2004, 5
    3, 1/2/2004, 1/1/2015, 6
];

INTERVAL:
NoConcatenate
LOAD DISTINCT Id, Dim_FromDt, Dim_ToDt
RESIDENT DIM;

Concatenate(INTERVAL)
LOAD DISTINCT Id, Fact_FromDt, Fact_ToDt
RESIDENT FACT;

/* Calendar table */
CYCLE:
NoConcatenate
LOAD DISPLAY,
     ACTUAL,
     ACTUAL_YYYYMMDD
FROM
\\Surfsd52\qlikviewstorage\Development\IDM\IDM_RPTG_AS_OF_DATES.QVD
(qvd);

IntervalMatch (ACTUAL)
LOAD Dim_FromDt, Dim_ToDt
RESIDENT INTERVAL;

IntervalMatch (ACTUAL)
LOAD Fact_FromDt, Fact_ToDt
RESIDENT INTERVAL;

[jimbiggs]

As a workaround, I created two separate Date tables, one for each IntervalMatch (Dim, Fact). I then use event triggers in the document to keep the two dates in sync. There may be downsides to this but so far it is working. The bad news is I found a couple of more tables I need to do this for so the triggers could get messy.

Let me know if you have any other ideas.  Thanks

[MarcoWedel · Accepted solution]

Hi Jim,

I tried to modify your script in order to create a link table and thus eliminating circular references.

The changes converted this data model

into that

Maybe this is how you expected the application to behave:

Here's my script:

SET DateFormat='M/D/YYYY';

DIM:
LOAD
  *,
  AutoNumberHash128(Id, Dim_FromDt, Dim_ToDt) as %DimId
INLINE [
    Id, Dim_FromDt, Dim_ToDt, Dim_Value
    1, 1/1/2000, 1/1/2015, 10
    2, 1/1/2000, 7/1/2013, 15
    2, 7/2/2013, 1/1/2015, 16
    3, 1/1/2000, 1/1/2015, 5
];

FACT:
LOAD
  *,
  AutoNumberHash128(Id, Fact_FromDt, Fact_ToDt) as %FactId
INLINE [
    Id, Fact_FromDt, Fact_ToDt, Fact_Value
    1, 1/1/2000, 8/1/2013, 10
    1, 8/2/2013, 1/1/2015, 11    
    2, 1/1/2000, 1/1/2010, 15 
    2, 1/2/2010, 1/1/2015, 16
    3, 1/1/2000, 1/1/2004, 5
    3, 1/2/2004, 1/1/2015, 6
];

/* Calendar table */
CYCLE:
LOAD
  *,
  AutoNumber(ACTUAL, 'DateID') as %DateID;
LOAD
  Round(Rand()*100) as DISPLAY,
  Date(AddMonths(Date#('1/1/2000','M/D/YYYY'), RecNo()-1), 'M/D/YYYY') as ACTUAL,
  Date(AddMonths(Date#('1/1/2000','M/D/YYYY'), RecNo()-1), 'YYYYMMDD') as ACTUAL_YYYYMMDD
AutoGenerate 180;

tabLink:
LOAD Distinct
  Id as LinkId
Resident DIM;

Join (tabLink)
LOAD Distinct
  Id as LinkId
Resident FACT;

Left Join (tabLink)
LOAD
  %DateID,
  ACTUAL
Resident CYCLE;

Left Join (tabLink)
IntervalMatch (ACTUAL, LinkId)
LOAD Distinct
  Dim_FromDt,
  Dim_ToDt,
  Id as LinkId
Resident DIM;

DROP Field Id From DIM;

Left Join (tabLink)
IntervalMatch (ACTUAL, LinkId)
LOAD Distinct
  Fact_FromDt,
  Fact_ToDt,
  Id as LinkId
Resident FACT;

DROP Field Id From FACT;

DROP Field ACTUAL From tabLink;

Left Join (tabLink)
LOAD Distinct
  Dim_FromDt,
  Dim_ToDt,
  LinkId,
  AutoNumberHash128(LinkId, Dim_FromDt, Dim_ToDt) as %DimId
Resident tabLink;

DROP Fields Dim_FromDt, Dim_ToDt From tabLink;

Left Join (tabLink)
LOAD Distinct
  Fact_FromDt,
  Fact_ToDt,
  LinkId,
  AutoNumberHash128(LinkId, Fact_FromDt, Fact_ToDt) as %FactId
Resident tabLink;

DROP Fields Fact_FromDt, Fact_ToDt From tabLink;

RENAME Field LinkId to Id;

hope this might help

regards

Marco

[jimbiggs]

Thanks Marco!  This approach works for me.  When I implemented it on my much larger tables though, it got quite large trying to match it up to every date value in my Cycle table.  I ended up limiting the Cycle date to only show month-end dates which made it more manageable.  Appreciate your assistance with this.