topic Re: Associate slow changing dimension to master table in Connectivity & Data Prep

Associate slow changing dimension to master table

adias102 — Tue, 28 Dec 2021 20:05:10 GMT

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.

Re: Associate slow changing dimension to master table

petter — Sun, 09 Feb 2020 09:54:45 GMT

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-intervals-to-discrete-data.htm#anchor-2

Re: Associate slow changing dimension to master table

petter — Sun, 09 Feb 2020 10:03:14 GMT

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/1479928