Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple fields in fact table referring to one dimension

Dear Qlik-Community,

I have the following situation and don't know how to handle this issue. I hope you know how to solve solve this problem.

I have a fact table with two different fields, which represent employees, and want to connect them to one dimension.

Here an example:

Fact-Table:

Article, ResponsiblePersonPurchasing, ResponsiblePersonSales

1, 20, 30,

2, 10, 30

3, 10, 20

etc.

DiM_Employees:

10, Mr. Maier

20, Mrs. Mueller

30, Mr. Max

Now i want to link the fields ResponsiblePersonPurchasing and ResponsiblePesonSales with DIM_Employee without using two dimensional tables.

Any ideas?

10 Replies
johanlindell
Partner - Creator II
Partner - Creator II

This is not an easy one but you can create a "link table". Is is not a elegant solution but it works.

Johan

Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

The alternative is splitting the records of your fact table into something like this:

Article, EmployeeID, Role

1, 20, Purchaser

1, 30, Seller

2, 10, Purchaser

3, 30, Seller

...etc

That will double the size of your fact table. I don't recommend it. Use two dimension tables instead.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

in fact there is difficult to recomend with no knowledgo of entire data model, analytical needs etc.

One of possible ways is:

table1:

Article_id, Article_name, Article_Group

1, A, G1

2,B,G1

3,C,G2

table2:

Article_id,Person_id,Person_type

1,20,Purchasing

1, 30,Sales

2, 10,Purchasing

2,30,Sales

3, 10,Purchasing

3,20,Sales

DiM_Employees:

Person_id,Name

10, Mr. Maier

20, Mrs. Mueller

30, Mr. Max

regards

Darek

rbecher
MVP
MVP

Hi,

following Ralph Kimball's approach of role-playing dimensions you would need to have two employee dimension tables in your case...

- Ralf

Astrato.io Head of R&D
simondachstr
Specialist III
Specialist III

Ever thought of using ApplyMaps? This way you can translate the IDs into Employee names fairly easy without adding any additional table to the data model.

rbecher
MVP
MVP

.. but then you will still have two employee fields, means two dimensions.

Astrato.io Head of R&D
Not applicable
Author

In "one field dimension" it will be ofcourse better to put names into fact table. But life is not that simple. I am sure, that there may be more complicated employee dimension, with some hierarchy, department info etc

regards

D

Not applicable
Author

I've used ApplyMaps, with that function you are not able to select one Employee and show all articles, which belongs to him, either it belongs to purchasing or to sales.

Not applicable
Author

So it is not possible to link two fields in the fact table to one dimension?

I think, it is only possible with two identic dimension tables.