Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Master Calendar for Multiple Dates

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

Model.png

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!

1 Solution

Accepted Solutions

Re: Master Calendar for Multiple Dates

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

View solution in original post

12 Replies
MVP
MVP

Re: Master Calendar for Multiple Dates

arsal_90
Contributor III

Re: Master Calendar for Multiple Dates

[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;

Re: Master Calendar for Multiple Dates

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

View solution in original post

arsal_90
Contributor III

Re: Master Calendar for Multiple Dates

[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
Contributor III

Re: Master Calendar for Multiple Dates

Then link it to master calendar

Not applicable

Re: Master Calendar for Multiple Dates

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?

Re: Master Calendar for Multiple Dates

Yes you are right.

Regards

Anand

MVP
MVP

Re: Master Calendar for Multiple Dates

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......

Not applicable

Re: Master Calendar for Multiple Dates

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