Help - Timestamp

[joris_lansdaal]

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?

[sunny_talwar]

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

[MK_QSL]

Re: Tidy up date/time field

[dmac1971]

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]

That doesn't work Sunny.

[sunny_talwar]

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]

No, same result.

[sunny_talwar]

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

];

[joris_lansdaal]

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

[sunny_talwar]

Can you share the script you used to do this?