Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
//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.
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
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
Search Canonical Date in Community... Blog by HIC