Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

Re: Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

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
Highlighted
Creator II
Creator II

Re: Data Model Problem with Mixed Fact/Dimension table

Hi,

Please attached file.

Thanks,

JaswantC

Highlighted
Partner
Partner

Re: Data Model Problem with Mixed Fact/Dimension table

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

Highlighted
Creator II
Creator II

Re: Re: Data Model Problem with Mixed Fact/Dimension table

Hi,

Please find updated one.

Highlighted
Partner
Partner

Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

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.

Highlighted
Creator II
Creator II

Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

Hi,

Can you check again my previous post?

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


Capture.PNG

Highlighted
Partner
Partner

Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

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.

Highlighted
Partner
Partner

Re: Re: Re: Re: Re: Data Model Problem with Mixed Fact/Dimension table

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