Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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!
Sure.... I will wait for your reply....
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
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...