10 Replies Latest reply: Mar 27, 2018 1:34 AM by Mohamed Ilyas Pitchai Mohamed Branched from an earlier discussion. RSS

    Re: Associating Master Calendar with Fact tables

    Mohamed Ilyas Pitchai Mohamed

      Hi Rob, i changed the data structures a bit and not Year has some issue which i cannot solve:

       

      Fact tables:

      PurchaseOrders:

      LOAD

      PURCHS_ORDR_ID,

      DATE(PURCHS_ORDR_DATE) AS PO_DATE,....

      (PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO) AS PO_CONT_COMP_KEY

      FROM [$(Fact)\PurchaseOrders.QVD]

      (qvd);

       

      PO_Cont_Comp_Key:

      LOAD

      (PURCHS_ORDR_NO & '-' & PURCHS_ORDR_DETL_NO) AS PO_CONT_COMP_KEY, MATRL_ID, SUPLR_ID

      FROM [$(Fact)\PurchaseOrders.QVD]

      (qvd);

       

       

      Contract_Lines:

      LOAD

      CONTRCT_NO AS CONTRACT_NO,

          DATE(CONTRACT_DATE) AS CONTRACT_DATE, ........

          (CONTRCT_NO & '-' & CONTRCT_DETL_NO) AS PO_CONT_COMP_KEY

      FROM [$(Fact)\Contract_Lines.QVD]

      (qvd);

       

      PO_Cont_Comp_Key:

      LOAD

      (CONTRCT_NO & '-' & CONTRCT_DETL_NO) AS PO_CONT_COMP_KEY, MATRL_ID, SUPLR_ID

      FROM [$(Fact)\Contract_Lines.QVD]

      (qvd);  

       

      // Common Datelink table for Calendar

      POContractDateLink:

      LOAD

      PO_CONT_COMP_KEY, PO_DATE AS PO_CONT_DATE, 'PUR' AS PO_CONT_TYPE

      RESIDENT PurchaseOrders;

       

      LOAD

      PO_CONT_COMP_KEY, CONTRACT_DATE AS PO_CONT_DATE, 'CON' AS PO_CONT_TYPE

      RESIDENT Contract_Lines;

       

      Temp:

      Load

      min(PO_CONT_DATE) as minDate,

      max(PO_CONT_DATE) as maxDate

      Resident POContractDateLink;

       

      TempCalendar:

      LOAD

      $(varMinDate) + IterNo()-1 As Num, Date($(varMinDate) + IterNo() - 1) as TempDate

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

       

       

      MasterCalendar:

      Load

      TempDate As PO_CONT_DATE, week(TempDate) As Week,

      year(TempDate) As Year, Month(TempDate) As Month, Day(TempDate) As Day,

      ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

      Week(weekstart(TempDate)) & '-' & weekyear(TempDate) as WeekYear,

      weekday(TempDate) as WeekDay

      Resident TempCalendar

      Order By TempDate ASC;

      Drop Table TempCalendar;

       

      Issue:

      Issue is, connected fields are empty in MasterCalendar. Like, Year, Week, Month are all empty.

      But i could see the value for PO_CONT_DATE and both MasterCalendar's PO_CONT_DATE is matching well with POContractDateLink's PO_CONT_DATE. Yet, i dont see Year values. Due to this, i cannot filter the POs by year.


      Let me know where is the disconnection happens.

       

      Regards

      Ilyas