11 Replies Latest reply: Jan 14, 2016 10:46 AM by Rob Wunderlich RSS

    Anyone know how to fix my MinMax table in this Master Calender script? It uses FieldValue()

    John Blomqvist

      Hello friends,

       

      In the script below I am trying to get my master calendar to work using a MinMax table that uses FieldValue function rather than a resident load of dates.

       

      But I cannot get the calender to work using this. Anyone got an idea of how to fix this?

       

      /////---------------------------Script-------------------------------------------

       

       

      Orders:

      LOAD * INLINE [

          OrderID, OrderDate

          1, 20/11/2011

          2, 20/11/2011

          3, 01/02/2012

          4, 31/01/2012

          5, 01/06/2014

          6, 15/07/2014

          7, 01/01/2016

      ];

       

       

       

      ////----Master Calender--------

       

      //MinMax1:

      //LOAD

      //    Min(OrderDate) AS MinDate1,

      //    Max(OrderDate) AS MaxDate1

      //RESIDENT Orders;

       

      MinMax:

      LOAD

      Min(FieldValue('OrderDate',RecNo()))-1 as MinDate,

      Max(FieldValue('OrderDate',RecNo())) as MaxDate

      AutoGenerate(FieldValueCount('OrderDate'));

       

      // ***** Set up the variables *****   

      LET vMinDate = NUM(PEEK('MinDate', 0, 'MinMax'));

      LET vMinDate2 = NUM(PEEK('MinDate', 0, 'MinMax'));

      LET vMaxDate = NUM(PEEK('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);

       

       

      // ***** Temporary Calendar *****

      TempCal:

      LOAD

          DATE($(vMinDate) + ROWNO()-1) AS TempDate

      AUTOGENERATE

          $(vMaxDate) - $(vMinDate) +1;

       

      //DROP TABLE MinMax;

       

      // ***** The Master Calendar *****

      MasterCalendar:

      LOAD

          TempDate AS OrderDate,

          WEEK(TempDate) AS Week,

          YEAR(TempDate) AS Year,

          MONTH(TempDate) AS Month,

          DAY(TempDate) AS Day,

          WEEKDAY (TempDate) AS Weekday,

          'Q' & CEIL(MONTH(TempDate) / 3) AS Quarter,

          CEIL(MONTH(TempDate)) AS Quarter1,

          DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

          WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

          INYEARTODATE(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

          INYEARTODATE(TempDate, $(vToday), -1) * -1 AS LastYTDFlag,

          MonthStart(TempDate) as MonthStart

       

       

      RESIDENT TempCal

      ORDER BY TempDate ASC;

       

       

      DROP TABLE TempCal;