Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IntervalMatch, Slowly Changing Dimensions and Calendar

Hi everyone,


My  HR data model is made mainly with slowly changing dimensions, which I have combined into one 'Sub Intervals' table.

I have six tables of data HR dependent on the time that I had to control using the interval match and subintervlas.

Basically my end result is being able to choose a point in time in calendar, and see the state of my employees based on the different slowly changing dimensions.

Data model:

Unbenannt.PNG

I have two cases:

1. When I round up date(ceil(SubIntervalEnd)) in script I have problem with SubIntervals table, this table create one additional interval (31.12.2016 - 01.01.2017) which is wrong and if I didn't remove this row from table I have wrong result, when I choose last date in calendar.

Unbenannt.PNG

2. When I don't round up date(ceil(SubIntervalEnd)), IntervalMatch in BridgeTableKalendar I don't see last day with  connection to interval(01.01.2017).

Unbenannt.PNG

Do you know what is the problem? Where I made a mistake and how can I solve it?

SubIntervalBegin - min date is 01.01.2014

SubIntervalEnd   -  max date is 01.01.2017

Script:


Let vEpsilon = Pow(2,-27);

//// ============ Load all distinct beginnings and endings Intervals ============

TempSubIntervals:
//Tabelee PA0000
Load distinct PA0000.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0000.BEGDA0001 as SubIntervalBegin  Resident PA0000;
Load distinct PA0000.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0000.ENDDA0001 as SubIntervalBegin  Resident PA0000;
//Tabelle PA0001
Load distinct PA0001.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0001.BEGDA0001 as SubIntervalBegin  Resident PA0001;
Load distinct PA0001.PERSONALNUMMER_TMP as PERSONALNUMMER, PA0001.ENDDA0001 as SubIntervalBegin  Resident PA0001;
... for rest table script looks the same


// ============ Create the SubIntervals ============

SubIntervals:
Load*,
PERSONALNUMMER & '|' & SubIntervalBegin  as SubIntervalID,
SubIntervalBegin & '-' & SubIntervalEnd as SubInterval.key;
Load
SubIntervalBegin  as SubIntervalBegin ,
date(ceil(SubIntervalEnd)) `      as SubIntervalEnd,
PERSONALNUMMER,
PERSONALNUMMER  as %PERSONALNUMMER.Personendaten,
Load PERSONALNUMMER
SubIntervalBegin,
Dual(SubIntervalEnd, (SubIntervalEnd - $(#vEpsilon))) as SubIntervalEnd
Where not IsNull(SubIntervalEnd) ;
Load PERSONALNUMMER, SubIntervalBegin,
If(PERSONALNUMMER = Previous(PERSONALNUMMER), Previous(SubIntervalBegin)) as SubIntervalEnd
Resident TempSubIntervals
Order By PERSONALNUMMER, SubIntervalBegin  Desc;
Drop Table TempSubIntervals;


TmpBridgeTablePA0000:
IntervalMatch (SubIntervalBegin, PERSONALNUMMER)
Load distinct PA0000.BEGDA0001PA0000.ENDDA0001, PA0000.PERSONALNUMMER_TMP as PERSONALNUMMER resident PA0000;

BridgeTable_PA0000:
Load
PERSONALNUMMER & '|' & SubIntervalBegin as SubIntervalID,
PERSONALNUMMER & '|' & PA0000.BEGDA0001 & '|' & PA0000.ENDDA0001 as PA0000.OriginalIntervalID
Resident TmpBridgeTablePA0000;


Drop table TmpBridgeTablePA0000;

TmpBridgeTablePA0001:
IntervalMatch (SubIntervalBegin, PERSONALNUMMER)
Load distinct PA0001.BEGDA0001PA0001.ENDDA0001, PA0001.PERSONALNUMMER_TMP as PERSONALNUMMER resident PA0001;

BridgeTable_PA0001:
Load

PERSONALNUMMER & '|' & SubIntervalBegin as SubIntervalID,
PERSONALNUMMER & '|' & PA0001.BEGDA0001 & '|' & PA0001.ENDDA0001 as PA0001.OriginalIntervalID
Resident TmpBridgeTablePA0001;

Drop table TmpBridgeTablePA0001;

... for rest table script looks the same

   TMP_Kalender:
LOAD
date(MinimumDatum + IterNo() - 1) as Datum,
date(MinimumDatum + IterNo() - 1) as %DATUM,
Month(date(MinimumDatum + IterNo() - 1)) as Monat,
Year( date(MinimumDatum + IterNo() - 1)) as Jahr,
Week( date(MinimumDatum + IterNo() - 1)) as Woche
WHILE MinimumDatum + IterNo() - 1 <= MaximumDatum;


LOAD
min(PA0000.BEGDA0001) as MinimumDatum,
   max(PA0000.ENDDA0001as MaximumDatum

RESIDENT PA0000;


NoConcatenate

Kalender:
LOAD
%DATUM,
// date(ceil(%DATUM)) as %DATUM,
Datum  as Kalender.Datum,
Monat  as  Kalender.Monat,
Jahr  as Kalender.Jahr,
MonthEnd(Datum) as Kalender.MonatJahr,
if(Datum = date(floor(MonthEnd(Datum))), floor(MonthEnd(Datum)),'') as Kalender.MonatJahrUltimo
Resident TMP_Kalender;

 

 

BridgeTableKalendar_TMP:
IntervalMatch (%DATUM)
Load Distinct SubIntervalBegin, SubIntervalEnd  Resident SubIntervals;


NoConcatenate


BridgeTableKalendar:
Load
(
SubIntervalBegin & '-' & SubIntervalEnd) as SubInterval.key,
SubIntervalBegin as TMPSubIntervalBegin,
SubIntervalEnd as TMPSubIntervalEnd,
%DATUM
Resident BridgeTableKalendar_TMP;

Drop table BridgeTableKalendar_TMP;

 

Thanks!

12 Replies
Not applicable
Author

Hi,

I checked,  in the application you will see that selection for 2017 in the table PA0000 chooses bad records. It seems to me that it's a problem with the enumeration subintervals.

What do you think?

swuehl
MVP
MVP

I think you are following pretty much the tech doc written by Henric

IntervalMatch and Slowly Changing Dimensions

but with slight modifications, which you should carefully review.

For example the original uses:

// ============ Create the bridge table between the two interval types ============

TmpBridgeTable:

IntervalMatch (SubIntervalEnd, SPID)

Load distinct OriginalBegin, OriginalEnd, TmpSPID as SPID resident OriginalIntervals;

BridgeTable:

Load

  SPID & '|' & SubIntervalEnd as SubIntervalID,

  SPID & '|' & OriginalBegin & '|' & OriginalEnd as OriginalIntervalID

  Resident TmpBridgeTable;

Drop Field TmpSPID;

Drop table TmpBridgeTable;



where you are using the SubIntervalBegin fields.

Not applicable
Author

It's working very good with table PA...

I had add additional flag for MonthYear in table InPerFTEEff and Fur PerFTEEff and it's working good.

Thanks for help!

BR,

Magda