Activites : A transaction table containing records of employees (by employee ID) who handled calls for a particular date.
Employees: A fact table containing information of all employees (Employee ID, Employee Name)
Employee_Report_Structure: A fact table containing information of which employee reports to which other employee (bosses and subordinates). Certain employees might report to a boss during a certain period, after which the employee structure changes and he reports to a different boss.
What I have done is to first use the Qlikview hierarchy function to create the staff hierarchy. After which I used interval match to match the dates the activities occur to try to find who the employee was reporting to during that particular date. Do note that the employee-boss hierarchy changes for different periods (as mentioned above).
Issue: I'm getting loops in my data structure as follows:
So I've solved it by first creating a composite key of ID & Date in the Activities table, then creating a temporary bridge table, after which creating a new bridge table of All the dates joined into one interval.