topic Re: Master Calendar for Multiple Dates in QlikView

Master Calendar for Multiple Dates

Mon, 10 Nov 2014 13:39:30 GMT

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!

Re: Master Calendar for Multiple Dates

MK_QSL — Mon, 10 Nov 2014 13:46:58 GMT

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

Re: Master Calendar for Multiple Dates

arsal_90 — Mon, 10 Nov 2014 14:00:39 GMT

[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

its_anandrjs — Mon, 10 Nov 2014 14:09:12 GMT

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

Re: Master Calendar for Multiple Dates

arsal_90 — Mon, 10 Nov 2014 14:12:15 GMT

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

Re: Master Calendar for Multiple Dates

arsal_90 — Mon, 10 Nov 2014 14:12:38 GMT

Then link it to master calendar

Re: Master Calendar for Multiple Dates

Mon, 10 Nov 2014 14:15:43 GMT

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

its_anandrjs — Mon, 10 Nov 2014 14:19:41 GMT

Yes you are right.

Regards

Anand

Re: Master Calendar for Multiple Dates

MK_QSL — Mon, 10 Nov 2014 14:35:14 GMT

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) ????

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

Do you think this will give the correct result?

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

Re: Master Calendar for Multiple Dates

Mon, 10 Nov 2014 14:52:14 GMT

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

Re: Master Calendar for Multiple Dates

MK_QSL — Mon, 10 Nov 2014 14:52:54 GMT

Sure.... I will wait for your reply....

Re: Master Calendar for Multiple Dates

its_anandrjs — Mon, 10 Nov 2014 15:09:04 GMT

Hi,

I believe there is no problem if you keep the rest of the date fields in each table that you have and on the basis of this date fields you create common date field and create master calendar also with over all minimum date and maximum date.After finding the min and max dates you can create the master calendar. And for any other calculation you can use the date fields which is available in the tables it self also. And add the table type flag in each table to filter data in the SET analysis by using flag field there see the script

CommonDateTable:

Load DISTINCT

%ID_GL,

LC_Date as CommonDate,

'Electric_Meter' as TableFlag

Resident Electric_Meter;

Load DISTINCT

%ID_GL,

EM_Date as CommonDate,

'Load _Curves' as TableFlag

Resident Load _Curves;

Load DISTINCT

%ID_GL,

Out_Start_Date as CommonDate

'Outages' as TableFlag

Resident Outages;

//Create master calendar like this way and load your rest of the table it self and keep date fields there.

MinMaxDate:

LOAD

Date(Min(CommonDate)) as MinDate,//Minimum date

Date(Max(CommonDate)) as MaxDate // Maximum date

Resident CommonDateTable;

Let vMinDate = NUM(PEEK('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxDate'));

TempDateTable:

LOAD

date($(vMinDate)+IterNo()-1) AS TempDatefield

AutoGenerate 1

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

MASTER_CALENDAR:

LOAD

TempDatefield as CommonDate,

DATE((TempDatefield),'YYYYMMDD') AS KEY_PERIOD,

DATE((TempDatefield),'YYYYMM') AS ID_TIME_YEARMONTH,

DATE(TempDatefield,'MMM-YY') AS ID_TIME_YEARMONTHDESC,

DATE(TempDatefield,'YYYYMMDD') AS ID_TIME_YEARMONTHDAY,

'Q'&ceil(Month(TempDatefield)/3) AS ID_TIME_QUARTER,

YEAR(TempDatefield) AS ID_TIME_YEAR,

MONTH(TempDatefield) AS ID_TIME_MONTH,

NUM(MONTH(TempDatefield)) AS ID_TIME_MONTH#,

NUM(DAY(TempDatefield)) AS ID_TIME_DAY,

WEEKDAY(TempDatefield) AS ID_TIME_DAY_WEEK,

NUM(TempDatefield) AS ID_TIME_DATE#

Resident TempDateTable;

DROP Table MinMaxDate;

DROP Table TempDateTable;

Regards

Anand

Re: Master Calendar for Multiple Dates

MK_QSL — Mon, 10 Nov 2014 15:11:44 GMT

But initially the Flag was missing ! That was my point... IF we are making more than one Dates to commondate, there should be a flag indicating difference between different tables..

Also, we have now Synthetic Key and Synthetic Table.... But it's OK as it will not affect the result...