2 Replies Latest reply: Jan 15, 2013 2:44 PM by Gysbert Wassenaar RSS

    Timestamp transformation

    phil rumbold

      I am loading a spreadsheet into Qlikview that contains timestamp data in one of the columns. I am using the wizard to create the load statement within the script and when I go into the 'transformation step' the timestamp column is transformed to a numeric field that contains a number of decimal places. As a consequence, I lose the format of the timestamp data. How can I retain the format so that I can then use the timestamp data as individual date and time fields within the presentation ?

        • Re: Timestamp transformation
          Jonathan Brough

          The number with decimal places sounds like QlikView's representation of the data and time combination.

          How about recreating the required date and time formats on the front end once you've loaded the date in ?

          Jonathan

          • Re: Timestamp transformation
            Gysbert Wassenaar

            Dates and timestamps in qlikview have both a numeric and a text (formatted) value. You can use the date(), time() and timestamp() functions to specify the textual formating. If you're loading a large number of records it's recommended to split the timestamp into separate date and time fields. Qlikview can store these more effeciently because the cardinality of the separate parts is a lot smaller than of the timestamp.

             

            time(frac(MyTimeStamp),'hh:mm:ss') as MyTime,

            date(floor(MyTimeStamp),'DD-MM-YYYY') as MyDate