topic Re: How to link master calendar in QlikView

How to link master calendar

Sat, 18 Mar 2017 13:04:00 GMT

Hi All,

Every time when i start work on QV project i get this issue of linking my Master Calendar with dates available in multiple tables.

This time i need to understand it correctly.

I have date field in 3 different tables and I have a Master calendar. I need to link date fields from 3 tables with Master Calendar date field.

I tried putting 3 different calendars for 3 tables. But this is not good programming practice.
Link table
Key concept

Appreciate any guidance on this and explanation.

Thanks,

Poonam

Re: How to link master calendar

vishsaggi — Sat, 18 Mar 2017 15:37:05 GMT

It is no harm if you load multiple Calendar's. However, it depends on your other data tables and the link between them.

Read here:

Why You sometimes should Load a Master Table several times

Re: How to link master calendar

ecolomer — Sat, 18 Mar 2017 17:36:41 GMT

You can create on Calendar this fields:

- Date as DateTable1

- Date as DateTable2

- Date as DateTable3

and then you have related each Table with Calendar

Re: How to link master calendar

rwunderlich — Sat, 18 Mar 2017 17:49:58 GMT

See this example for a tutorial:

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to link master calendar

jananireddy — Wed, 11 Oct 2017 07:27:20 GMT

Hi Poonam, Any solution to this question.. i have a similar issue

My closest analysis came from this link Canonical Date

Kindly let me know if there is a way where we can get away with one table only for dates

Re: How to link master calendar

rwunderlich — Wed, 11 Oct 2017 14:13:41 GMT

Janani,

The cookbook link I posted above has a good pattern for handling multiple dates.

-Rob

Re: How to link master calendar

jananireddy — Thu, 12 Oct 2017 07:07:06 GMT

Hi Rob, Thankyou this works for me in most charts.

In one chart however i see a problem in sorting. Using the field 'Month' from the table common calendar i am unable to sort my x axis

Re: How to link master calendar

rwunderlich — Fri, 13 Oct 2017 01:19:50 GMT

I don't think you want to sort by expression. You want to sort Numerically if they are proper dates.

-Rob

Re: How to link master calendar

jananireddy — Fri, 13 Oct 2017 05:49:26 GMT

Rob, These are month fields, in the format Jul - 2017, Jun - 2017 ....

Numerically is does not sort

The dimension is having the expression =month(DateMaturity)&'-'&year(DateMaturity)

Thanks for the help so far

Janani

Re: How to link master calendar

Anonymous — Fri, 29 Dec 2017 04:05:08 GMT

Hi Rob,

Thanks for the sharing. May I know if you have the solution as well, if within the same table I have multiple date fields?

Is it possible to link them to the same Master Calendar?

Thanks & Regards,
Peter

Re: How to link master calendar

Anil_Babu_Samineni — Sat, 30 Dec 2017 02:35:53 GMT

There is no way to split from single table. But could be work using multi loads like

Fact:

LOAD Date1 as DateField, 'Date1' as Flag, Sales FROM Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Date2:

LOAD Date2 as DateField, 'Date2' as Flag, Sales FROM Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Date3:

LOAD Date3 as DateField, 'Date3' as Flag, Sales FROM Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

CalendarMaster:

LOAD Date(DateField) AS Date,

Load Date(MinDate + IterNo() -1) AS Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load Min(DateField) AS MinDate, Max(DateField) AS MaxDate RESIDENT Fact;

Re: How to link master calendar

pradosh_thakur — Sat, 30 Dec 2017 02:50:11 GMT

https://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

Does this help ? HIC got a solution for you here i guess.

Re: How to link master calendar

rwunderlich — Tue, 02 Jan 2018 18:52:29 GMT

Yes, as Anil suggested, load each date in a separate load statement. There should be no difference in the pattern whether the dates are in the same or different tables.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: How to link master calendar

pnkulwal — Sat, 06 Apr 2024 02:02:39 GMT

Thank you rwunderlich!