Using Interval Match And Hierarchy Together

[#1]

Hi,

So I have the following tables:

• 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:

Any ideas on how I can solve the loop?

[#2]

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.

[#3]

Hi New_Qliker,

Try the following :

1)Link Employees EMP_ID with StaffHierarchy EMP_ID

Also, In StaffHIerarchy, load EMP_ID as empID(just an alias for a diff column name to get rid of that synthetic Key)

2) Rename Activities EMP_ID as empID.

This should work.You dont need a bridge table in this case,as you have only one fact.

Hope this helps.

Please get back to me with your result or any further queries.

Rochelle