Modeling role-playing dimension

[valpassos]

Hi community,

I'm in need of some light regarding a what I believe is a role-playing dimension scenario.
Imagine I have something like a Employee-Manager relationship, in which a Manager is an employee as well.

In my visualizations, I need to see certain fact for my Employees, and at the same time I need to see the same fact for the respective Managers.

How do I accomplish this? Knowing that I only have an Employee dimension, and the relationship between Employee-Manager is in a Relationship dimension (because I can have multiple relationship types between a Employee and his Manager), in which I've created an hierarchy. So, when I select the employee (the child), I can see who is his manager (father).

My Employee dimension and Relationship dimension are linked by the %EmployeeKey, and my fact table is linked to the Employee dimension by the %EmployeeKey as well.

In essence: how do I access the Manager's facts in my visualizations?

Thank you!,
Lisa

[miskinmaz]

You can follow this link:

https://community.qlik.com/t5/QlikView-Documents/Hierarchy-function-easy-and-Efficient-with-example/...

[valpassos]

Hi @miskinmaz !

Thanks for your reply. I have already created the hierarchy between Employee/Manager, but I believe that doesn't solve all my problems.

I want to be able to, for example, in accessing a particular event date in an expression, refer to the Employee event date, as well as the Manager event date. Like:

MAX({<...>} Particular_Event_Date)

Both Employee and Manager will have different dates, and I want to compare them.

Lisa

[valpassos]

Forgot to clarify: right now, when I calculate the MAX(... Event_Date) in my chart expression, I'm accessing the Employee event date, as that is the one linked to my fact table. I need to access the Manager event date as well.

[valpassos]

Anyone? 😃

[miskinmaz]

Based on Manager ID you can again link your employee table with different field name to get the fact related information of manager.

[valpassos]

Hi @miskinmaz ,

You mean this?

LEFT JOIN (Employee)
LOAD EmployeeID,
ManagerID
RESIDENT HierarchyTable;

Or do you mean creating another Employee dimension with the Manager ID instead of Employee ID?

But then how do I link it to my fact table? How do I differentiate between Employee and Manager when creating my expressions?

Thanks,

Lisa

[miskinmaz]

For example

Load

EMP ID

Manager id,

From your hierarchy table.

Load 

EMP ID as Manager id,

EMP Name as ManagerName,

EMP Desgn as ManagerDesgn

...

from emp_table.

[valpassos]

@miskinmaz ,

Still not getting how exactly will I distinguish between Employee facts and Manager facts in my front-end expressions 😕

How are you linking those 2 tables to the fact table?

Lisa

[kuba_michalik]

I would model it like this, for this specific isolated case - not sure how well it would gel with the rest of the data model and all the other things that need to be shown.

First I'd create a new table for the chart dimension:

EmpNewDim:
Load
    Employee as Employee_new_dim,
    'EMP' as FactLinkageType,
    %EmployeeKey
Resident Employee;

Then, add links to manager facts for each employee:

Concatenate (EmpNewDim)
Load
    Employee as Employee_new_dim,
    'MGR' as FactLinkageType,
    %MgrKey as %EmployeeKey
Resident Relationship;

I don't know if you have the necessary fields in the Relationship table, but you should be able to add them easily. The idea here is to take the hierarchy and connect it to the data in reverse. Typically hierarchies work so that manager connects to the facts through their employees; what we want is for every employee to connect to the data through themselves, and additionally through their managers. We are adding the FactLinkageType field to be able to distinguish the two types of linkage from each other.

In the chart you use Employee_new_dim as dimension (which of course has all the same values as original Employee dimension) and the formulas
MAX({<FactLinkageType={'EMP'}>} Particular_Event_Date)
MAX({<FactLinkageType={'MGR'}>} Particular_Event_Date)
to get the employee event and manager event shown by employee.

Everything should work just fine as long as users won't be making selections in Employee_new_dim expecting it to be just the same as regular Employee dimension 😉

I assumed you just want to have this for a single hierarchy level (so only for the employee - direct manager relationship)