Master calendar

[#1]

Hi,

I have some problems creating a Master Calendar with the right connections to the other tables without creating a syntetic key or a loop.

This is what I have:

• Table 1 - Transactions
  • transactionID
  • assetID
  • sessioniD
  • transactionDate
• Table 2 - Assets
  • assetID
• Table 3 - Sessions
  • sessionID
  • assetID
  • sessionDate
• Table 4 -Master Calendar
  • ID
  • date
  • month
  • day
  • ...

The connections is like this:

1. Table 1 -> Table 2 (assetID) and Table 3 (sessionID)
2. Table 3 -> Table 2 (assetID)

Now I need help to find a good solution where I can join Table 1 and Table 3 into Table 4 with the dates without getting a syntetic key or a loop.

Thanks ahead!

[#2]

That makes sense now. Thank you - it works great.

Isabel

[peschu123]

Hi,

I'am quite a beginner in Qlikview. But handling dates is quite tricky at all, especially with more than 1 Fact table and for beginners like me. Handling of dates was the last thing I thought about at the beginning of my first QV project. 

For my first QV App(I actually developing) I needed this common date and I used Robs solution with a Common Calendar and single calendars. Thank you 2 at this point for your help. (rob for the solution and henric for the hint leading to it..). Now I have some problems with the data model. Its pretty clean, but the "connection" between some tables(keyfields) does't work as expected(1 problem solved and a new arises..). But I think I hopefully can solve this with generic keys(henric without your articles I would have thrown my pc out of the window many times ^^). But I'm a bit afraid if the key/linktables will become to huge for the application...(?!) Sorry for OT.

The complexity I ran into, to "just" create a common date surprised me a bit. I didn't expected this in the beginning. A problem with that in the real world is, to explain this to other people(customers,supervisors, colleagues). If they don't develop QV Apps they hardly understand why this should be a problem("...just choose a date, a date is a date...blabla"). They are used too just choose a year,month etc. and never think of whats behind that. And I think this is quite normal if you don't want to compare some facts with different date dimensions. In my case, all dates in my database are different. Despite, it doesn't matter for the "end-user" which date is underlying for the chart, if the right "date dimension" was chosen for the chart/expression.

My users just want one date dimension/selector in the App. Multiple date selectors make the surface pretty confusing(ok perhaps this is my fault too, you would say...).

I don't know if this helpful, but it's my experience and perhaps there will be a better solution for that some time. If I have no hair in 3-5 years, than QV is the reason. ^^

- PS -