9 Replies Latest reply: Jun 26, 2015 8:31 PM by Steven Blower RSS

    Simplifying date and timestamp

      I just received a large data set and all the dates in the Excel document are written as "MM/DD/YYYY HH:MM:SS". This is problematic because when I load the data and create a line chart, for example, each entry is listed as a separate date if they were created on the same day but the time they were created was different. Do I need to address this in Excel or is there a way for me to use QlikSense to only look at the MM/DD/YYYY part?

       

      Thanks for your help in advance!

        • Re: Simplifying date and timestamp
          Sunny Talwar

          Try this:

           

          Date(Floor(Date#(DateField, 'MM/DD/YYYY hh:mm:ss')), 'MM/DD/YYYY') as DateField

          • Re: Simplifying date and timestamp
            Steven Blower

            Hi Daniel

             

            If you are still seeing multiple values it is possible you have another field that is causing each value to display rather than just displaying one entry for each unique date.

             

            Everyone above is quite right that using the floor function to remove the time portion will truncate the timestamp leaving just the date element.  In fact if you format this as a timestamp you would get e.g. 23/10/2014 00:00:00 as there is no time element left after the floor function has been used.

             

            If there is another field in your table that has a different value against each of the identical truncated dates then this will cause more than one of the date values to show in your table.

             

            By example, a simple script of:

             

            Example:
            Load * inline
            [
            ID,Date
            1,23/09/1998 06:03:31
            2,09/05/2013 10:50:02
            3,13/06/2003 15:30:53
            4,27/01/2006 00:42:43
            5,03/01/2007 06:26:44
            6,18/02/2011 07:56:22
            7,02/11/2000 03:18:09
            8,26/01/2002 08:32:15
            9,27/01/2006 00:42:43
            10,03/01/2007 06:26:44
            11,18/02/2011 09:42:20
            12,02/11/2000 03:18:09
            13,26/01/2002 09:45:17
            14,27/01/2006 23:53:10
            15,03/01/2007 06:26:44
            16,18/02/2011 07:22:54
            17,02/11/2000 03:18:09
            18,26/01/2002 08:29:42
            19,27/01/2006 00:42:43
            21,03/01/2007 06:26:44
            ]
            ;

            left join (Example)
            Load
            ID,
            Timestamp(Date,'DD/MM/YYYY hh:mm:ss') as FormattedTimestamp,
            Timestamp(Floor(Date),'DD/MM/YYYY hh:mm:ss') as FlooredTimestamp,
            Date(Floor(Date),'DD/MM/YYYY') as TruncatedDate
            Resident Example;

            will generate a few dates to play with.

             

            You will see that the ID field is unique for each date but there are some dates where the date is the same but the time is different.

             

            Dropping these fields into a table will result in:

            You can see here how the floor function has removed the time element and then by formatting the floored result as a date in DD/MM/YYYY format you get the date result you want.  However, you will also see that some of the dates are present more than once in the right most column.  This is because the ID field is unique to the dates and as the ID field is included in the table then each related occurrence of the date will be displayed.  To prevent the multiple dates from showing up you would need to remove the unique fields.  So by removing the ID and formatted timestamp fields the result is:

            and the multiple values have gone.

             

            Not sure if this helps but hopefully it will.

             

            Kind regards

             

            Steve