Single employee table multiple "roles" for an employee.

[SBN]

Hi there fellow Qlikkies,

I'm trying to figure out a way to accommodate a single "Employee" table in my data model and have different "roles" for the employees without duplicating the "Employee" table in separate "role" tables.

Attached is a demo app with a simplified version of a more difficult data model:

The data model has an "Orders" table which contains sales orders which contains a reference to SalesPerson to whom the value of the order is linked. And an Author who's registered the order.
It has a "Customer" table which holds customers with a AccountManager who's responsible for the customer.
And it has an "Employee" table which contains all the employee related information.
I've tried to connect this all together through a "Roles" table, but end up with the synthetic key and circular loop...;-)

Is it possible to have a single employee table for different roles in a data model?
If so can you help me out with suggestions or if you're willing to put in the effort remodel the data model in the example app?

Thanks in advance,

SBN

[vincent_ardiet_]

You could try with a Roles table containing OrderID, EmployeeID, CustomerID, Role with this last field containing the value "SalesPerson" or "AccountManager".
You will of course have to remove CustomerID and SalesPersonID from table Orders and also AccountManagerID from Customers.

[rwunderlich · Accepted solution]

I took a crack a remodeling it like this (qvf attached). 

Downside is that you won't be able to easily show Customer + Account Manager Name for Customers who don't have Orders. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[vincent_ardiet_]

That's why I was suggesting to have the link with the customer in RoleLink, else you can't have an account manager without orders. 

[SBN]

@vincent_ardiet_ & @rwunderlich,

Thank you both for your time and efforts to help me out with this subject. Really appreciate it!

I used a combination of both of your answers to set it up in the example app.
Now the resulting straight tables show the relationships between the different datasets and roles.

@rwunderlich uses the "only()" function in a measure for the customer "Account manager", the orders "Sales person" and order "Author" in order to show them in one line in a straight table.

Is there a possibility to turn these measures into dimensions to use them in selections?
I know you can create an aggregate dimension, but the selection would than be either on "Customer" or "Order" instead on the "Employee name"...😁

Or do you have to add them as a separate duplicated table per role of the "Employee" table in your data model?
And explain to the user of the dashboard that they need to switch selections based on the role they want to analyse?

Thanks again!!

SBN

[rwunderlich]

You can turn them into Dimensions and control what field gets selected by what Field you use in the Aggr. 

=Aggr(only({<Role={'Author'}>}Name), Name)

https://qlikviewcookbook.com/2016/01/scoping-selections-with-aggr/

If you want to have a Dimension named "Author" and have the current selections show as "Author" I believe you will have to duplicate the Name field as Author either in the RoleLink or the Employee table.

-Rob

[SBN]

Hi @rwunderlich ,

I just realized I forgot to thank you for your additional suggestion.
My apologies!
Thank you!

Kind regards,

SBN

[SBN]

@rwunderlich and @vincent_ardiet_ ,

The combination of both of your suggestions has probably solved the challenge I had.
I created the "RoleLink" table.
That works like charm.
With regards to the dimensions I choose to use "If(Role="...", ...)" expressions.
Not the optimal solution I guess, because it adds a second selection "Role".
And might be a bit more confusing to the user.
On the other hand does the dashboard show the information related to that particular role of the employee.

Thanks again!

Kind regards,

SBN

P.S. Because both of your efforts helped me in the right direction who would like me to accept the solution of the other one? 😉

[vincent_ardiet_]

Good to know that it helped.
You could select both I think, else Rob deserves it more than me, I'm less than a Padawan compared to his Jedi master skills 🙂

[SBN]

@vincent_ardiet_ 

Thanks, I will!
Your last sentence must be something from Star Wars. I recognize the "Jedi" part. The "Padawan" part doesn't ring a bell with me, sorry...😁