Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Modeling role-playing dimension

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

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Modeling role-playing dimension

Hi @valpassos ,

Yeah, I guess I do - you are making selections in the original Employee dimension (which only links employees to their own facts - so after you make selections there, the manager data becomes excluded and that's why it does not show anything).

I can think of three ways to resolve that, but all have cons:

- don't create the links to manager facts in a separate dimension table, but instead concatenate it to the main Employee dimension. Con: you need to set up set analysis everywhere, or formulas would mix data for employee and their manager together

- clear the selection for Employee in the formula, and "copy" it to the new Employee dimension (the one with the link types). Syntax for such a set would be something like {<FactLinkageType={'MGR'}, Employee=, Employee_new_dim=$::Employee>}. Con: formulas become a little confusing 😉

- change your approach completely. Instead of creating links, just take manager facts and re-join them to the fact table (so you would have 2 columns: EventDate and ManagerEventDate), then build your expressions on the appropriate columns. Con: it is data duplication, and it is less flexible if you would have to account for multiple relationship types or multiple hierarchy levels at some point

Depending on your preferences, I think the second or third option would be best.

View solution in original post

14 Replies
Highlighted
Creator III
Creator III

Re: Modeling role-playing dimension

Highlighted
Creator II
Creator II

Re: Modeling role-playing dimension

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

Highlighted
Creator II
Creator II

Re: Modeling role-playing dimension

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.

Highlighted
Creator II
Creator II

Re: Modeling role-playing dimension

Anyone? 😃

Highlighted
Creator III
Creator III

Re: Modeling role-playing dimension

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

 

Highlighted
Creator II
Creator II

Re: Modeling role-playing dimension

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

Highlighted
Creator III
Creator III

Re: Modeling role-playing dimension

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.

Highlighted
Creator II
Creator II

Re: Modeling role-playing dimension

@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

 

 

Highlighted
Partner
Partner

Re: Modeling role-playing dimension

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)