7 Replies Latest reply: Feb 28, 2018 12:29 PM by Pablo Labbe RSS

    Master Calendar to Hours Issues - world of pain :)

    Alexander James Tomlins

      Hi Guys,

       

      I can't share any data sorry but happy to share script - I was hoping to get some help on the auto generate section in my master calendar. I have concatenated many of my data sources but i'm now trying to link it to a master calendar. I can do this down to Day but my master calendar isn't generating hours

       

      I am trying to link my data table to the master calendar on  [DateHour] which is created and in the format of DD/MM/YYYY hh. I did this by concatenating a Date and hour function on a DateTime field. The Master calendar script is simple standard daily one from the Qlik help videos lol

       

      I think i might be a bit confused - it looks like my min and max date in Temp table are also not in the right format.

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12); 


      Temp: 

      Load 

      min([DateHour]) as minDate, 

      max([DateHour]) as maxDate 

      Resident Data; 


      Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

      DROP Table Temp; 


      TempCalendar: 

      LOAD 

      $(varMinDate) + Iterno()-1 As Num, 

      Date($(varMinDate) + IterNo() - 1) as TempDate 

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


      MasterCalendar: 

      Load 

      Date(TimeStamp(Floor(TempDate)), 'DD/MM/YYYY') As Date, 

      Date(Date(TempDate, 'DD/MM/YYYY hh:mm:ss:fff'), 'DD/MM/YYYY hh') AS [DateHour],

      week(TempDate) As Week, 

      Year(TempDate) As Year,

      Month(TempDate) As Month,

      Dual(Date(TempDate, 'MMM-YY'), MonthEnd(TempDate)) AS MonthName,

      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;