Associate slow changing dimension to master table

[adias102]

Hi everyone, I need your help with this issue.

I have a main EVENT table. For each event I have an EventID (unique key) and several attributes including EventDate (datetime) and EmpID (foreign key).

I need to associate the EMPLOYEE table but the employee attributes change over time so this table is SCD type 2. Thie means for each EmpID I have a OpenDate, CloseDate and CurrentFlag (1 if this is the current record, 0 if else). The unique key for this table is a sequence number.

The 2 tables should be associated based on the EmpID and the date (EVENT.EventDate between EMPLOYEE.OpenDate and EMPLOYEE.CloseDate).

Each employee is updated on a different schedule based on its attributes, so each employee has 1 or more records.

For example, EmpID 123:

The 2 first records for this EmpID in EVENT should be associated to the first EmpID record in EMPLOYEE.

The 3rd record in EVENT should be associated to the 3rd record in EMPLOYEE.

Please advise. 

[petter]

You have a paricular section of the Qlik Sense documentation explaining how to resolve this:

Using the extended intervalmatch syntax to resolve slowly changing dimension problems

ttps://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/LoadData/matching-int...

[petter]

And this is an older article on Qlik Community - although it refers to QlikView everything is relevant to Qlik Sense since the load script language and data model architecture is almost 100% identical between the products:

https://community.qlik.com/t5/QlikView-Documents/IntervalMatch-and-Slowly-Changing-Dimensions/ta-p/1...