Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

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

Hi,

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

tblFacts:

Load

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

     CourseRef&'-'&CourseDate as %_CourseRefDate,

     'Employees' as MetricType

     countEmployee as DataValue

Resident

     dimTraining

;

Concatenate (tblFacts)

Load

     Null() as %_CourseDateEmp,

     CourseRef&'-'&CourseDate as %_CourseRefDate,

     'Events' as MetricType,

     countEvent as DataValue

Resident

     dimEvents

;

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.

Thanks

Adam

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