Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Using Multiple Dates With Master Calendar

Dear All,

I am trying to associate dates from multiple tables with master calendar.

Scenario:

Suppose we have two tables.

The first table connects with the second table using a Key field

The first table also connects with the master calendar but using the date1 field.

The second table also connects with the master calendar using the date2 field.

What is the best way to avoid loops while still being able to use master calendar?

Please find the document attached.

Regards & Thanks,

-Khaled

P.S:

Note1: Not all tables are inter-connected, so it's a loosely-connected data model and some tables may remain isolated.

Note2: Tables may or may not be connected to each other but date filters from each table should show all dates in the date fields (which is why the master calendar is in place)

Tags (1)
1 Solution

Accepted Solutions

Re: Using Multiple Dates With Master Calendar

I would start by removing the calendar completely and get the other 7 tables linked properly first. I think that there are tables or keys missing...

Data model.png

Then I would add one calendar per date. Why? See here.

Finally, I would add a generic calendar for all dates. This can be tricky to achieve, but usually it is possible to link the most detailed level of the facts, e.g. order line, to multiple dates using a link table. The link table will then in turn link to the generic calendar. In other words, the link table should have three fields: OrderLineID, DateType ('Payment date', 'Invoice date', 'Order date', etc.), and Date.

HIC

4 Replies

Re: Using Multiple Dates With Master Calendar

I would start by removing the calendar completely and get the other 7 tables linked properly first. I think that there are tables or keys missing...

Data model.png

Then I would add one calendar per date. Why? See here.

Finally, I would add a generic calendar for all dates. This can be tricky to achieve, but usually it is possible to link the most detailed level of the facts, e.g. order line, to multiple dates using a link table. The link table will then in turn link to the generic calendar. In other words, the link table should have three fields: OrderLineID, DateType ('Payment date', 'Invoice date', 'Order date', etc.), and Date.

HIC

Not applicable

Re: Using Multiple Dates With Master Calendar

Hello Henric,

This is it. I initially assumed that I would have to load the master calendar multiple times but wasn't any sure if it would be the best approach.

Thank you once again

Regards,

-Khaled.

Not applicable

Re: Using Multiple Dates With Master Calendar

Hi Henric,

Thank you for your solution,I'm experiencing the same problem...I've linked all my tables correctly but I can't seem to get the the Master Calendar working with the 3 dates...Can you please attach a sample to further explain your solution.

Looking forward to hearing from you

Regards

Eugene

Re: Using Multiple Dates With Master Calendar

Well, do you really want to connect the master calendar to all three dates? It would mean that when you click on "February", you get orders that were placed in Feb (OrderDate), and orders that were ordered muche earlier but payed in Feb (InvoiceDate), and orders that were ordered earlier and payed later, but required in Feb (RequiredDate). Not very simple and clear for the user...

Read this before you decide: http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles. My recommendation is to have one calendar per date.

The question is whether to have a "generic calendar" in addition. A generic calendar needs a bridge table, created through e.g. (or more complicated if the dates reside in different tables)

Load 'OrderDate' as Type, OrderID, OrderDate as GenericDate From Orders ;

Load 'InvoiceDate' as Type, OrderID, InvoiceDate as GenericDate From Orders ;

Load 'RequiredDate' as Type, OrderID, RequiredDate as GenericDate From Orders ;

And then you connect your Master Calendar to the GenericDate.

HIC