topic Re: help with timestamp in QlikView

help with timestamp

hopkinsc — Tue, 27 May 2014 13:40:36 GMT

Hi All,

I have a field with a timestamp, the values of this field are formatted like this...

1306052300376

This is broken up as follows..

13 = Year

06 = Month

05 = Day

23 = Hour

00 = Mins

37 = Secs

6 = Extract counter (I want to ignore this)

How can i reformat these in the script to be a seperate date field (DD/MM/YYYY) and a seperate time field (hh:mm)

Any help would be great

rbecher — Tue, 27 May 2014 13:44:18 GMT

Hi,

you can use:

date(date#(left(1306052300376, 6), 'YYMMDD'), 'DD/MM/YYYY') ..for date

=time(time#(mid(1306052300376, 7, 4), 'hhmm'), 'hh:mm') ..for time

- Ralf

eduardo_sommer — Tue, 27 May 2014 13:49:28 GMT

MakeDate('20'&Mid(Field,1,2),Mid(Field,3,2),Mid(Field,5,2)) as NewDate

MakeTime(Mid(Field, 7,2), Mid(Field, 9,2), Mid(Field,11,2)) as NewTime

Where Field is the name of the field with the timestamp (substitute for your field)

Eduardo

its_anandrjs — Tue, 27 May 2014 13:52:10 GMT

In load script load a table like below

LOAD *,

Date(Date#(Left(OldDate,6),'YYMMDD'),'DD/MM/YYYY') as NewDate,

Time(Time#(Mid(OldDate,7,6),'hhmmss'),'hh:mm:ss') as NewTime;

LOAD * Inline

[

OldDate

1306052300376

];

Or in front end try with this in any text object

For Date

Date(Date#(Left(1306052300376,6),'YYMMDD'),'DD/MM/YYYY')

For Time

Time(Time#(Mid(1306052300376,7,6),'hhmmss'),'hh:mm:ss')