Howdy. I've searched through the forum, but not found a post which quite addresses this question. This one was close, but represented the opposite problem. The "similar posts" tool found more results han my searching: this post, this one, and this one cover some solutions, but don't discuss which is best, or more generally what advantages and disadvantages exist. What's easiest? What's most commonly used? Has anyone used one of these solutions for 12 months or so and has some feedback on how well it's worked?
A common practice in data warehouses is to have a Dates table which gives values such as year, quarter, and month for each day. It looks like this is common in QlikView too, and the name "calendar table" seems to be widely-used. If this calendar table has a Date field as its key, any queries which also contain a Date field will automagically join to it. Groovy!
But... what do you do when you have multiple fact tables, with different date fields, which should not join together? For example, say there's a Customers.Date field representing the date the customer was created, and an Orders.Date field representing the date an order was placed. A report which contains both fields will need to rename at least one of them, breaking the join.
Also, a fact table may contain multiple date dimensions. In my case, the Orders table has OrderDate, DatePaid, and ShipDate fields. I could create three more calendar tables, with key fields of OrderDate, DatePaid, and ShipDate, respectively. This seems cumbersome; is this the best practice, or is there a simpler option?
In SQL, I'd write three JOINs, to the same table each time but specifying a different field from the first table each time. But QlikView seems to be restricted to natural joins, which as a DBA makes me wince.
Instead of using one or more calendar tables, you could just import the date values as dates, and use QlikView date functions to populate your date hierarchies. Is this what most users end up using?
I appreciate any input. Thank you!
