Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shanemichelon
Partner - Creator II
Partner - Creator II

Data Model Problem with Mixed Fact/Dimension table

Hi everyone.  I have a problem trying to setup a data model that works for a case with a fact table that is also a dimension table.  I have created a simplified version to illustrate (attached).

Basically, I have a concatenated fact table with payroll data and hazard data (and other stuff) in it.  This table has keys of date/employee/location.  Location is only used for hazard data. It is null for the other records.

I also have a staff table, which also has a location field (staff working location).  I also need the location table because it has many other fields that I need (postcode/building size/etc).

This is the data model that is created by QV with synthetic keys:Mixed Fact and Dimension.png

As you can see in the simple pivot table, If I try to get pay by firstname and amount, it doesn't work.

I have reviewed many documents and sample regarding link tables, but the difference is that my second fact table is also a dimension table.

Please help, I can feel my brain melting!

1 Solution

Accepted Solutions
shanemichelon
Partner - Creator II
Partner - Creator II
Author

I think I have solved this myself.  Effectively we need to treat employee plus location as a single combined key.  The fact tale will then have a dimension of %EmployeeCodeLocation.  When the fact table is populated, build the %EmployeeLocation key as you go.  If one of the two bits is not relevant just leave it blank.  Then load the link table from this.  Create one record for every record in the fact table.  Simply use SubField to break the %EmployeeLocation filed into the two parts to link to staff and locations dimension tables.

Here is the new data model:

Mixed Fact and Dimension Solved Data Model.png

Here is the UI view:

Mixed Fact and Dimension Solved Tables.png


I have attached the solute for reference.

Thanks and I hope this is useful to someone.

View solution in original post

7 Replies
Anonymous
Not applicable

Hi,

Please attached file.

Thanks,

JaswantC

shanemichelon
Partner - Creator II
Partner - Creator II
Author

Thank you for your incredibly fast reply!  However there is a problem with the solution.  In the data there is a hazard for location 100 (Head Office).  If you Create a list box for locations and select Head Office, it excludes the Hazard.  Ie Hazards are not tied to locations:

Mixed Fact and Dimension 2.png

Ideally, I would like to create a dashboard table that shows hazards by location and amount paid to staff by location.  Eg:

Location           No Hazard  Payroll Total

Head Office          15              $4,000

Branch Office      4                  $1,000

At the moment, it looks like this:

Mixed Fact and Dimension 3.png

Anonymous
Not applicable

Hi,

Please find updated one.

shanemichelon
Partner - Creator II
Partner - Creator II
Author

Thanks again for your assistance.  However, the table still does not link hazards to locations:

Mixed Fact and Dimension 4.png

I have attached your solution with a pivot table showing the summary info.

Anonymous
Not applicable

Hi,

Can you check again my previous post?

Here again i am attaching same file with additional metric what you are looking.


Capture.PNG

shanemichelon
Partner - Creator II
Partner - Creator II
Author

Thanks again for your continued assistance.

However, I think I made the example a little too simple. In my actual case, I have other metrics about the Hazards.  Such as Hazard type code, description etc.  In you sample, you have eliminated the hazard table and not added it to the fact table.  How can we accommodate extra hazard info such as a hazard type, hazard description etc.

shanemichelon
Partner - Creator II
Partner - Creator II
Author

I think I have solved this myself.  Effectively we need to treat employee plus location as a single combined key.  The fact tale will then have a dimension of %EmployeeCodeLocation.  When the fact table is populated, build the %EmployeeLocation key as you go.  If one of the two bits is not relevant just leave it blank.  Then load the link table from this.  Create one record for every record in the fact table.  Simply use SubField to break the %EmployeeLocation filed into the two parts to link to staff and locations dimension tables.

Here is the new data model:

Mixed Fact and Dimension Solved Data Model.png

Here is the UI view:

Mixed Fact and Dimension Solved Tables.png


I have attached the solute for reference.

Thanks and I hope this is useful to someone.