0 Replies Latest reply: Jul 29, 2013 9:38 AM by sohini777 RSS

    How to Create a link table and master calendar from multiple dates

      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.