topic Re: Master calendar in QlikView

Master calendar

Wed, 24 Oct 2012 14:55:15 GMT

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:

Table 1 -> Table 2 (assetID) and Table 3 (sessionID)
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!

Re: Master calendar

mphekin12 — Wed, 24 Oct 2012 15:21:27 GMT

Since Transaction Date and Session Date aren't really related, I would suggest that you create a master calendar for each one. Here is a blog that may be of help:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

Re: Master calendar

Wed, 24 Oct 2012 17:11:48 GMT

Hi,

What I missed to say was that I would want only one calendar in the application where I can choose a date and where both the transaction date and session date would adaped to the chosen date.

Thanks!

Re: Master calendar

rwunderlich — Wed, 24 Oct 2012 19:24:52 GMT

You can build a link table and create your calendar off the link table.

DateLink:

LOAD

assetID, sessionID, transactionDate as Date

,1 as TransactionCounter

RESIDENT Transactions

;

Concatenate

LOAD

sessionID, assetID, sessionDate as Date

,1 as SessionCounter

RESIDENT Sessions

;

Then build your calendar from the Date field. For example, if you were using Qvc:

CALL Qvc.CalendarFromField('Date');

That leaves you with a synkey for assetID & sessionId, but you had the synkey before the calendar. You can take care of the synkey in the same link table:

When you load your Transaction and Session tables, create a compostie key:

AutoNumber(assetID &'|'& sessionID) as %Asset_Session_Key

Include the %Asset_Session_Key field in the DateLink table:

LOAD %Asset_Session_Key, assetID, sessionID, transactionDate as Date....

Drop the redundant field from the fact tables:

DROP FIELDS assetID, sessionID FROM Transactions, Sessions ;

-Rob

http://robwunderlich.com

Re: Master calendar

Anonymous — Wed, 24 Oct 2012 19:41:45 GMT

Hi edrik,

find the attachment of common date issue of excel,i created a common calendar based on two transaction tables.

i given blue print of data model also.if you have any problem give me sample data i will provied solution to your questions.

Re: Master calendar

hic — Wed, 24 Oct 2012 19:47:05 GMT

In my experience, the demand of having just one calendar table is an extremely limiting one. And it is also confusing for the end user. A filter on the SessionDate can never be the same as a filter on the TransactionDate. So I do not think it should be presented as the same either.

If you have just one calendar, you cannot make a selection like SessionDate='Aug' AND TransactionDate='Sep'. Which is something the user may want to do.

I have had this discussion many times, and it is always about the calendar table. No one ever suggests to use the same master Organization table for CustomerID, ShipperID and SupplierID, although the problem is identical. For some reason, it is easier to conceptually accept to load the master Organization table three times, one for each key, than to do it for the master calendar table.

But if you still want one single calendar, you should do what Rob suggests.

HIC

Re: Master calendar

rwunderlich — Wed, 24 Oct 2012 20:57:30 GMT

Henric,

In my experience, customers frequently want to view activity grouped by a common time dimension. "How many units ordered, shipped, invoiced by Month". A common calendar makes this a simple exercise for the chart designer.

Using a common calendar does not limit the design to have only one calendar. You can use both common and fact calendars in the same application. Using both supports common rollups as well as the scenario you presented (SessionDate='Aug' & TransactionDate='Sep')..

-Rob

Re: Master calendar

hic — Wed, 24 Oct 2012 21:11:36 GMT

You're of course right that you can combine the two solutions and have the best of both worlds. I have also done exactly this myself among a number of customers. But first after having had exactly this discussion with the customer...

My point is only that it ought to be natural to start with one calendar per date, and then optionally - if needed - add one generic table. But often the discussion is the opposite.

But as with many things in QlikView - there is not just one way to skin a cat...

HIC

Re: Master calendar

Wed, 15 May 2013 18:00:26 GMT

Hi Rob,

You post has helped me a lot and I am hoping that you may have a simple solution to help me modify your code to get a year as well as a date field.

I have three tables that all link to a date and so I am trying to create a master calander. I think it is working fine however I need to check more examples of my data. I would like to have a list of years as well as the actual dates. I have had a go at modifying the code however I must have it wrong as there is selecting a date does not select the relevent year (and vice versa) as you can see from my screen shot below, Iwhen I select December 2011, all years remain selected in my CalanderYear field.

This is the code I have written:

DateLink:

LOAD
Participant_Name, Event, F_Attendance_Date as Date
,1 as Attendance_Counter
RESIDENT Attendance_Table;

Concatenate

LOAD
Event, F_Event_Date as Date
,1 as Event_Counter
RESIDENT Events_Table;

Concatenate

LOAD
Participant_Name, Owner_Date as Date
,1 as Owner_Counter
RESIDENT Result;

LOAD

year(Date) as CalanderYear
Resident DateLink

Your help would be much appreciated,

Isabel

Re: Master calendar

rwunderlich — Thu, 16 May 2013 04:40:33 GMT

The Calander Year field you are creating is in a new table and is not linked at all to the DateLink table.

Simplest solution is to create Year in the table when you create the Date fields.

LOAD
Participant_Name, Event, F_Attendance_Date as Date,

,Year( F_Attendance_Date) as Year

,1 as Attendance_Counter
RESIDENT Attendance_Table;

Re: Master calendar

Thu, 16 May 2013 08:03:08 GMT

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

Isabel

Re: Master calendar

peschu123 — Thu, 16 May 2013 08:07:00 GMT

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 -