3 Replies Latest reply: Feb 6, 2014 2:54 PM by Chuck Cadman RSS

    Converting Timestamp into Date

      Hey,

      I know that this question has already been touched on a few different times but after about 3 hours of searching and trying different methods, I still can't convert my timestamp field into a date. I admit that I am also very new to QlikView and may have been using some of these methods incorrectly. What I'm trying to do is convert the field [Resolved Date], which looks like 5/4/2011 5:08PM, into a field with just the date, so that it would look like 5/4/2011. However, I don't want to lose the time in respect to place keeping. So if you have two different records (e.g. 1. 5/4/2011 5:08PM and 2. 5/4/2011 1:34PM; I'd want it to show as 1. 5/4/2011 and 2. 5/4/2011 in a list box). I am using the personal version of QlikView and my goal is to use this formatted date in a slider sheet object to be able to drill down data so I'm guessing that I will need to divide the date into year, month, and day fields similiar to how the tutorial did it. Any information or tips on going about would be very much so appreciated but please be thorough in your explanation.

      Methods I've Tried:

      - year(date#([Resolved Date], 'MM/DD/YYYY')) as Year_Date, in the load statement

      - date#([Resolved Date], 'MM/DD/YYYY') as Date, in the load statement

      - date(floor([Resolved Date],'MM/DD/YYYY')) in the expression tab of a list box*

      - date(ceil([Resolved Date],'MM/DD/YYYY')) in the expression tab of a list box*

      - date(daystart([Resolved Date],'MM/DD/YYYY')) in the expression tab of a list box*

      * I've done this with and without the formatting, as well as switching the number formatting under the Number Tab

        • Converting Timestamp into Date
          Leonard Short

          You are going to need to add something to differentiate between the two matching year dates (5/4/2011) or you won't be able to see the two dates in the same column.

           

          As you are looking at individual day's and presumably the time's are important for analysis at some point? Perhaps an intervalmatch on the time would be useful to put those times into individual buckets? You just need to identify the 'Time' field in your original dataset.

           

          TimeRange:
          LOAD * INLINE [
          'Start Range', 'End Range', 'Time Range'
          00:00, 01:00, 00:00-01:00
          01:00, 02:00, 01:00-02:00
          02:00, 03:00, 02:00-03:00
          03:00, 04:00, 03:00-04:00
          04:00, 05:00, 04:00-05:00
          05:00, 06:00, 05:00-06:00
          06:00, 07:00, 06:00-07:00
          07:00, 08:00, 07:00-08:00
          08:00, 09:00, 08:00-09:00
          09:00, 10:00, 09:00-10:00
          10:00, 11:00, 10:00-11:00
          11:00, 12:00, 11:00-12:00
          12:00, 13:00, 12:00-13:00
          13:00, 14:00, 13:00-14:00
          14:00, 15:00, 14:00-15:00
          15:00, 16:00, 15:00-16:00
          16:00, 17:00, 16:00-17:00
          17:00, 18:00, 17:00-18:00
          18:00, 19:00, 18:00-19:00
          19:00, 20:00, 19:00-20:00
          20:00, 21:00, 20:00-21:00
          21:00, 22:00, 21:00-22:00
          22:00, 23:00, 22:00-23:00
          23:00, 24:00, 23:00-24:00
          ]
          ;

           

          Discharge_Interval:
          IntervalMatch([Time]) Load [Start Range], [End Range] Resident TimeRange;

           

          You will most likely want to add fields for year, quarter, month, day, weekday, etc.. as well as time to your original dataset. This will give you lots of buckets to use in your charts but will also allow you to drill down to specific hours once you have identified the dates you want to review.

          • Re: Converting Timestamp into Date

            Thank you for your reply but it turns out my solution was much simpler then I had made it sound. Luckily there was someone else were I work that showed me what to do. In order to convert a timestamp into a date (atleast for me), you must go into your load statement and type the following at the end of your field names and just before the FROM keyword:

             

            date(timestamp#([Your Timestamp Field Name],'M/DD/YYYY h:mm'), 'M/DD/YYYY') as NewDate

             

            This will take your timestamp field ("Your Timestamp Field Name") and convert it into the date you want ("NewDate") without the time on it. How this works is that it takes a timestamp with the format 'M/DD/YYYY h:mm' and simply changes that format into a date format 'M/DD/YYYY'.

             

            Tips: If you have seconds on your timestamp make sure to add :ss after the mm otherwise the dates in your NewDate field will be scrambled. Secondly, make sure that the FROM keyword below your statement is blue otherwise all of this will not reload properly. If it is not blue, make sure there is no comma after your statement.