Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.


Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Data Modelling Advice


Could do with some data modelling advice for a QlikView data model with training data. I've not got access to the app right now so I can't copy the script or print screen the data model...so I'll have to explain.

I have 2 tables - dimTraining and dimEvents.

dimTraining: the lowest level in this table is CourseRef, CourseDate and EmployeeRef. This is a table of all the courses created over the days they run on. There is a countEmployee field which is a count of the records.

dimEvents: the lowest level in this table is CourseRef, CourseDate (just data on the events). This is a table of which employees are booked on each course by day. There is a countEvent field which is a count of the records.

note: not all the CourseRef values in dimEvents exist in dimTraining, only once employees are booked on the course.

I've created a Facts table using resident loads from these dimension tables

The Facts table links to dimTraining on a compound key %_CourseDateEmp

The Facts table links to dimEvents on a compound key %_CourseDate



     CourseRef&'-'&CourseDate&'-'&EmployeeRef as %_CourseRefDateEmp,

     CourseRef&'-'&CourseDate as %_CourseRefDate,

     'Employees' as MetricType

     countEmployee as DataValue




Concatenate (tblFacts)


     Null() as %_CourseDateEmp,

     CourseRef&'-'&CourseDate as %_CourseRefDate,

     'Events' as MetricType,

     countEvent as DataValue




So I have a 3 table data model. tblFacts associating with dimTraining on %_CourseRefDateEmp and tblFacts associating with dimEvents on %_CourseRefDate.

The issue I have is that when I filter for an Employee (the field existing in the dimTraining table) it filters tblFacts for the associated %_CourseRefDateEmp values. My expression "=Sum({<MetricType={Events}>}DataValue)" will return nothing as that key is loaded as Null() for the Events data (as it is at a different granularity).

My question is what's the best way to handle mixed granularity in a facts table in this instance? The main thing I need to get from this is being able to filter my events data in the facts table by employee selections.... i.e. if i select an employee ref I'd like to see how many events they have been booked on to.

I'd prefer not to OuterJoin the 2 dimension tables if possible.

Any help / advice appreciated.



1 Reply
MVP & Luminary
MVP & Luminary

Re: Data Modelling Advice

Perhaps this blog post helps: Fact Table with Mixed Granularity

talk is cheap, supply exceeds demand