Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

joris_lansdaal
Contributor

Help - Timestamp

Hi all,

in several logfiles I encounter the following Timestamp '20160421T102714.000+0200'.

Currently I have: SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

The problem I have is that I can't convert it to Date, Week, Month... as a matter of fact I can't use it at all.

Any suggestions?

Tags (2)
1 Solution

Accepted Solutions

Re: Help - Timestamp

Could be the version issue. What about this:

=Date(Floor(TimeStamp#(PurgeChar(Left(EventsTimestamp, 15), 'T'), 'YYYYMMDDhhmmss')))

19 Replies

Re: Help - Timestamp

May be like this:

Date(Floor(TimeStamp(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss')))

Month(Floor(TimeStamp(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss')))

MonthYear(Floor(TimeStamp(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss')))

etc...

MVP
MVP

Re: Help - Timestamp

dmac1971
Contributor II

Re: Help - Timestamp

Hi download and study this application :

QlikView How To (or Tips & Tricks) Application

It covers some of the main sticking points when using Qlikview.

Dermot

joris_lansdaal
Contributor

Re: Help - Timestamp

That doesn't work Sunny.

timestamp.PNG

Re: Help - Timestamp

My bad, I forgot to add # next to TimeStamp

Date(Floor(TimeStamp#(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss')))

Month(Floor(TimeStamp#(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss')))

MonthYear(Floor(TimeStamp#(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss')))

joris_lansdaal
Contributor

Re: Help - Timestamp

No, same result.

Re: Help - Timestamp

Seems to be working for me:

Table:

LOAD *,

  Date(Floor(CalculatedTimeStamp)) as Date,

  Month(CalculatedTimeStamp) as Month,

  Year(CalculatedTimeStamp) as Year,

  MonthName(CalculatedTimeStamp) as MonthYear,

  Time(Frac(CalculatedTimeStamp)) as Time,

  Hour(CalculatedTimeStamp) as Hour,

  Minute(CalculatedTimeStamp) as Minute,

  Second(CalculatedTimeStamp) as Seconds;

LOAD TimeStamp,

  TimeStamp#(Left('20160421T102714.000+0200', 15), 'YYYYMMDDThhmmss') as CalculatedTimeStamp;

LOAD * Inline [

TimeStamp

20160421T101026.000+0200

20160421T101456.000+0200

20160421T101901.000+0200

20160421T102000.000+0200

20160421T102128.000+0200

20160421T102130.000+0200

20160421T102214.000+0200

20160421T102216.000+0200

20160421T102500.000+0200

];

Capture.PNG

joris_lansdaal
Contributor

Re: Help - Timestamp

Am I doing something very wrong......?

table.PNG

Re: Help - Timestamp

Can you share the script you used to do this?