3 Replies Latest reply: Jan 31, 2018 8:16 AM by Ali Hijazi RSS

    Issue with mismatching date formats

    Alexander James Tomlins

      Qlik Sense Gurus Assemble!

       

      Hi guys,

       

      I am having trouble associating on a date field between my master calendar date field and my fact table.

       

      I have got a date time data type but is but the data is aggregated by hour so e.g '31/01/2018 01:00:00:00'

       

      I want to get my master calendar is currently coming out as  ''31/01/2018 01:00:00:11' and so there is no link.

       

      Has anyone got any advice for me pretty please?

       

      the master calendar script is as follows, the date hour is in bold - this is the one I am struggling to get to '31/01/2018 01:00:00:00' ;

       

      Temp: 

      Load 

      min(SubscribedDateTime) as minDate, 

      max(SubscribedDateTime) as maxDate 

      Resident Subscriptions; 

           

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

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



      LET vDays = varMaxDate - varMinDate + 2;


      MasterCalendar:

      LOAD

      Date(Floor(DateHour)) AS Date,

      DateHour,

        Hour(DateHour) AS Hour,

        Week(DateHour) AS Week,

        Year(DateHour) As Year, 

        Month(DateHour) As Month,

        MonthName(DateHour) As MonthName,

        WeekYear(DateHour) AS [WeekYear],

        Month(DateHour) & '-' & Year(DateHour) as MonthYear, 

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

        WeekDay(DateHour) as WeekDay,

        Minute(DateHour) AS Minute;


      LOAD

      Timestamp(Timestamp($(varMinDate)) + ((RecNo() - 1)/24)) as DateHour

       

      AUTOGENERATE 24 * $(vDays);


      DROP Table Temp;