Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master calendar

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!

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

11 Replies
mphekin12
Specialist
Specialist

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

Not applicable
Author

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!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable
Author

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.

hic
Former Employee
Former Employee

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

CommandAndFactCalendar.png

hic
Former Employee
Former Employee

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

Not applicable
Author

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.

year date error.png

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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;