3 Replies Latest reply: Sep 29, 2016 5:34 PM by Matthew Cummings RSS

    DateTime Datatype Woes, differences to the right of the decimal

    Matthew Cummings

      Hello my fine Qlik friends!

       

      After a few hours research, I found no answer to this question: Can I truncate a double float fractional part to a given precision?

       

      Problem:  I have a dataset containing various datetime objects.  In one table, I have an event, a Start Time and an End Time.  It is loaded like this:

      Schedule:

      LOAD [Clndr Date] AS Date,

           [Agent Corp Login] AS Login,

           Code,

           Descr,

           Time(Frac([Start Moment]), 'hh:mm:ss') AS [Start Moment],

           Time(Frac([Stop Moment]), 'hh:mm:ss') AS [Stop Moment],

           Interval(Time([Stop Moment])-Time([Start Moment]), 'mm') AS Duration

      FROM

      [..\2.UserData\Schedule.xlsx]

      (ooxml, embedded labels, table is [Schedule]);

       

      In another table I have 30 minute time intervals defined in a spreadsheet: 

       

      Interval StartInterval End
      0:00:000:29:59
      0:30:000:59:59
      1:00:001:29:59
      1:30:001:59:59

       

      I use Intervalmatch to link the Start Time to a given Interval and this works well. ...almost.  When I threw the resulting dataset into the report block to verify, I noticed NULL values:

      1.jpg

      Using some recently learned troubleshooting techniques, I created a Listbox showing both the formatted values and the Num() values and noticed this: 

      2.jpg

      3.jpg

      This discrepancy is consistent for all non-matching rows.  The last 4 digits to the right of the decimal are different.  I've tried various attempts at Round(), I also toyed with a few other likely functions but what I think I need is some consistent way to lop off or restrict the last few digits of the Start Time and perhaps the Interval Start as well.


      Any ideas are greatly appreciated.


      Qlik On!


      mfc