Master Calendar for Multiple Dates

[#1]

Hello,

I have a data model with three tables: Load Curves, Electric Meters and Outages.

Each table has a unique ID known as %ID_GL. There are also different dates:

• Load Curves: LC_Date
• Electric Meters: EM_Date
• Outages: Out_Start_Date

Previously I had a Master Calendar only for the Load Curves table, but what I need now is to use the Master Calendar for the three tables at the same time, so that when I select a particular date or range of dates, I would see the data filtered for all the tables.

I was told that maybe I should use a link table by ID and date in the middle of the data model to achieve what I'm trying to do.

Do you know how could I do this?

Thank you!

[MK_QSL]

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

[arsal_90]

[Link Table]:

Load distinct %ID_GL,

EM_DATE

Resident ELECTRIC_METERS;

concatnate ([Link Table])

Load distinct %ID_GL,

LC_DATE

Resident LOAD_CURVES;

concatnate ([Link Table])

Load distinct %ID_GL,

OUT_START_DATE

Resident OUTAGES;

[its_anandrjs · Accepted solution]

Hi,

You can try to rename this all different field as common dates and then using this date with master calencar

CommonDateTable:

Load

%ID_GL,

LC_Date as CommonDate

Resident Electric_Meter;

Load

%ID_GL,

EM_Date as CommonDate

Resident Load _Curves;

Load

%ID_GL,

Out_Start_Date as CommonDate

Resident Outages;

And then create master calendar connect with date field CommonDate

Regards

Anand

[arsal_90]

[Link Table]:

Load distinct %ID_GL,

EM_DATE as MASTER_DATE,

'Electricmeters' as TYPE

Resident ELECTRIC_METERS;

concatnate ([Link Table])

Load distinct %ID_GL,

LC_DATE as MASTER_DATE,

'LoadCurves' as TYPE

Resident LOAD_CURVES;

concatnate ([Link Table])

Load distinct %ID_GL,

OUT_START_DATE as MASTER_DATE,

'Outages' as TYPE

Resident OUTAGES;

[arsal_90]

Then link it to master calendar

[#7]

Thanks for your reply!

I have the following Master Calendar:

LK_DATE_TMP:

LOAD *,

  DATE(MAKEDATE(ID_TIME_YEAR,ID_TIME_MONTH,ID_TIME_DAY),'YYYYMMDD') AS KEY_PERIOD,

  DATE(MAKEDATE(ID_TIME_YEAR,ID_TIME_MONTH),'YYYYMM') AS ID_TIME_YEARMONTH,

  DATE(MAKEDATE(ID_TIME_YEAR,ID_TIME_MONTH),'MMM-YY') AS ID_TIME_YEARMONTHDESC,

  DATE(MAKEDATE(ID_TIME_YEAR,ID_TIME_MONTH,ID_TIME_DAY),'YYYYMMDD') AS ID_TIME_YEARMONTHDAY,

  'Q'&ceil(ID_TIME_MONTH/3) AS ID_TIME_QUARTER

;

LOAD ID_TIME_DATE,

  YEAR(ID_TIME_DATE) AS ID_TIME_YEAR,

  MONTH(ID_TIME_DATE) AS ID_TIME_MONTH,

  NUM(MONTH(ID_TIME_DATE)) AS ID_TIME_MONTH#,

  NUM(DAY(ID_TIME_DATE)) AS ID_TIME_DAY,

  WEEKDAY(ID_TIME_DATE) AS ID_TIME_DAY_WEEK,

  NUM(ID_TIME_DATE) AS ID_TIME_DATE#

;

LOAD

  DATE(MAKEDATE(2007,1,1)+(ITERNO()-1),'YYYYMMDD') AS ID_TIME_DATE

AUTOGENERATE 1

WHILE DATE(MAKEDATE(2007,1,1)+(ITERNO()-1)) <= DATE(MAKEDATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())));

MASTER_CALENDAR:

NOCONCATENATE

LOAD DISTINCT

  *

RESIDENT LK_DATE_TMP

WHERE EXISTS(EFFECTIVE_DATE,ID_TIME_DATE);

// THE DATA IS REDUCED TO THOSE DATES WHO HAVE AN "EFFECTIVE DATE" IN THE TABLE

DROP FIELD EFFECTIVE_DATE;

DROP TABLE LK_DATE_TMP;

So I should use the CommonDate from your sample code as the EFFECTIVE_DATE in the Master Calendar I'm using, right?

[its_anandrjs]

Yes you are right.

Regards

Anand

[MK_QSL]

Dear demo user....

Not the best way to just give a CommonName to all dates...

Consider that you are now going to find out Average KW_TOTAL per day.... how you will find?

SUM(KW_TOTAL)/COUNT(Distinct CommonDate)    ????

or

SUM(KW_TOTAL)/(Max(CommonDate)-Min(CommonDate)+1) ????

Do you think this will give the correct result?

Bit curious to know your thoughts......

[#10]

Thanks for your reply. Let me see how it works and I'll let you know.