topic Re: Timestamp transformation in QlikView

Timestamp transformation

philrumbold — Tue, 15 Jan 2013 08:51:49 GMT

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

Anonymous — Tue, 15 Jan 2013 09:00:12 GMT

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 — Tue, 15 Jan 2013 19:44:40 GMT

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