Hi guys looking for some help,
I am trying to create a link table from 3 diferent tables that contain three different date fields. I then want to create one master calendar for the application.
Tables:
FactoryData - Date
SupplierData - SupplierCreatedDate
CorrectiveActions - ModifiedDate
I have tried the following but not having much luck as there is no association with anything.
//---------Calendar Link Table----------------------
Link:
LOAD
%SupplierKey,
Date AS LinkDate,
'FactoryDate' AS DateType
RESIDENT FactoryData;
CONCATENATE (Link)
LOAD
%SupplierKey,
SupplierCreatedDate AS LinkDate,
'SupplierDate' AS DateType
RESIDENT SupplierData;
CONCATENATE (Link)
LOAD
%CorrectiveActionKey AS %CorrectiveActionKey_Link,
[Modified Date] AS LinkDate,
'CorrectiveActionDate' AS DateType
RESIDENT CorrectiveActionData;
//--------------------------Master Calendar--------------------------------------------
TempMinMax:
LOAD
MIN(LinkDate) AS MinDate,
MAX(LinkDate) AS MaxDate
RESIDENT Link;
Let vMinDate = num(Peek('MinDate', '0', MinMax));
//LET vMinDate = NUM(ADDMONTHS(YEARSTART(PEEK('MinDate',0)),-24));
LET vMaxDate = PEEK('MaxDate',0,'TempMinMax');
MasterCalendar:
LOAD
LinkDate,
WEEK(LinkDate) AS Week1,
MONTH(LinkDate) AS Month1,
'Q'&CEIL(MONTH(LinkDate) /3) AS Quarter1,
IF(WEEK(LinkDate)=53 AND num(MONTH(LinkDate))=1, WEEK(LinkDate)&'-'&numsum(YEAR(LinkDate)-1), WEEK(LinkDate)&'-'&YEAR(LinkDate)) AS WeekYear1,
DUAL(MONTH(LinkDate)&'-'&YEAR(LinkDate),YEAR(LinkDate)&MONTH(LinkDate)) AS MonthYear1,
'Q'&CEIL(MONTH(LinkDate) /3)&' '&YEAR(LinkDate) AS QuarterYear1,
YEAR(LinkDate) AS Year1
;
LOAD
$(vMinDate) + recno() -1 AS Num,
DATE($(vMinDate) + recno() - 1) AS LinkDate
AUTOGENERATE
($(vMaxDate) - $(vMinDate) + 1);
DROP TABLE TempMinMax;
Also attached is a view of the data model.
Any help or solution would be appreciated.
Thanks in advance.
