Hi,
Personally, I don't tend to have calendar tables in separate tables in my data model, rather create various date parts in each table, like this:
LOAD
[From Date Time],
Date(DayStart([From Date Time]), 'DD MMM YYYY') as [From Date],
Year([From Date Time]) as [From Year],
Month([From Date Time]) as [From Month Name],
Date(MonthStart([From Date Time]), 'MMM-YY') as [From Month],
[To Date Time],
Date(DayStart([To Date Time]), 'DD MMM YYYY') as [To Date],
Year([To Date Time]) as [To Year],
Month([To Date Time]) as [To Month Name],
Date(MonthStart([To Date Time]), 'MMM-YY') as [To Month],
I wrote a blog post on why I prefer this approach.
This doesn't deal with the problem of gaps in dates though. In some ways, having the gaps helps see where there are missing data, and I would therefore say is a good thing.
You could create a calendar QVD and load from it many times, once for each date, e.g.
FromCalendar:
LOAD
Date as [From Date],
Month as [From Month],
Year as [From Year],
[Month Name] as [From Month Name]
FROM [lib://qvd/Calendar.qvd] (qvd)
;
If you don't want redundant dates at the start and end of each date table you will need to do the max min on each field and use this in a WHERE statement on the QVD. This processing can be slow though.
A better approach may be to create an events table and build the calendar on this. This will give you a single calendar and you can then pick what type of date you are looking at. To do this, first load your MainData table and ensure it has a Unique Key. Then load into an Events table for each date in the table:
Events:
LOAD
1 as DateCount,
UniqueKey,
'From Date' as DateType,
[From Date] as Date
RESIDENT MainData;
Events:
LOAD
1 as DateCount,
UniqueKey,
'To Date' as DateType,
[To Date] as Date
RESIDENT MainData;
You can then hang your calendar table off of the Date field or build it into the table.
This is particularly nice if you have things like support tickets to track, as you can have a line chart with the sum of DateCount on it and a dimension of DateType, then each line would be for things like Tickets Opened, Tickets Closed, Tickets Suspended etc..
As all of these techniques involve doing the same thing many times (once for each date you have) you may want to look into using variables with parameters in them, but these can get a bit fiddly.
Hopefully that points you in the right direction.
Steve
