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
