How to create a master calendar if we have two or more date fields in different tables. I want to use the same master calendar for all purposes

[#1]

//Table1

[Availability]:

LOAD

ID,

AVAIL_SLICE_DATE,

AVAIL_SLICE

;

//Table2

[Allocation]:

LOAD

ID,

PROJECT_NAME,

ALLOC_SLICE_DATE,

ALLOC_SLICE

;

In the above load, scripts I have two date fields AVAIL_SLICE_DATE, ALLOC_SLICE_DATE in Tables 1 and Tables 2 respectively.

Now I have to create a master calendar and link it both the tables. If it is only one table then I can have the same name for the connecting fields of MasterCalendar and Table1. Now since the master calendar should also connect to Table2, how should I do. If I give the same connecting name to Table2 also it will produce {ID+ connecting name} as synthetic key.

Could you please help me how to do?

Thanks in advance.

[ashfaq_haseeb]

Hi look at the below post.

http://community.qlik.com/docs/DOC-6502

or

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

Regards

ASHFAQ

[amit_saini]

Hi,

Rename all your data fields to one common name , suppose as "Datefield" and  Use this script for Master Calender:

LET vMinDate = num(makedate(2010));

LET vMaxDate = num(today());

Datefield:

LOAD

    $(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

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

GenCal:

LOAD

    Datefield as %DateID,

    //Datefield as %DateID2,

//    date(Datefield) as Date,

    dual(date(Datefield, 'MMM YY'), year(Datefield)*100+month(Datefield)) as MonthYear,

    year(Datefield) as Year,

    month(Datefield) as Month,

    day(Datefield) as Day,

    //week(Datefield) as Week,

    week(Datefield) & ' / ' & year(Datefield) as Week,

    weekday(Datefield) as Weekday

RESIDENT Datefield;

drop table Datefield;

Thanks,
AS

[MK_QSL]

Search Canonical Date in Community... Blog by HIC