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
