topic Re: Help - Timestamp in QlikView

Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 14:57:33 GMT

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?

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 15:06:42 GMT

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...

Re: Help - Timestamp

MK_QSL — Wed, 08 Jun 2016 15:07:38 GMT

Re: Tidy up date/time field

Re: Help - Timestamp

dmac1971 — Wed, 08 Jun 2016 15:11:12 GMT

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

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 18:31:48 GMT

That doesn't work Sunny.

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 18:34:39 GMT

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')))

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 18:53:11 GMT

No, same result.

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 19:00:18 GMT

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

];

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:11:47 GMT

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

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 19:13:26 GMT

Can you share the script you used to do this?

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:16:04 GMT

I do have a solution by breaking up the timecode. Like,

Month(Date#(Left(Timestamp,8),'YYYYMMDD')) as Month.

But I am curious in what rooky misstake i'm making.

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 19:17:28 GMT

True, would you be able to share the script you used??

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:22:28 GMT

Here's the QVW.

When I ran the original from the server it worked fine (I found this QVW inside the community. Then I copied the content to document and further wanted to developed it, colors etc; because i have a personal edition.

The only thing bothering me is that timestamp....

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 19:25:03 GMT

I was not able to find TimeStamp field, but there was EventsTimeStamp field. Check this:

=Date(Floor(TimeStamp#(Left(EventsTimestamp, 15), 'YYYYMMDDThhmmss')))

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:33:23 GMT

Amazing.........

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:34:21 GMT

The field Timestamp is dropped in the Time Tab

Re: Help - Timestamp

sunny_talwar — Wed, 08 Jun 2016 19:35:38 GMT

Could be the version issue. What about this:

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

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:39:43 GMT

THANK YOU!

Re: Help - Timestamp

joris_lansdaal — Wed, 08 Jun 2016 19:41:14 GMT

Thanks for helping me out. Great document!

Re: Help - Timestamp

Colin-Albert — Wed, 08 Jun 2016 19:41:24 GMT

Hi Joris

A good overview of issues related to date & time data are in these posts...

Why don’t my dates work?

Get the Dates Right

QlikView Date fields