topic constructed timestamp, ?how in qlikview? in QlikView

constructed timestamp, ?how in qlikview?

Mon, 15 Jun 2009 12:00:03 GMT

Hello,

My query gives me, among other things, a date like '05-27-2009', a value of hours, like 3 or 21 and a value of minutes, calculated to be either 00, 15, 30, or 45.

?So how do I turn that into a qlikview timestamp that I can use as a line-graph dimension?

constructed timestamp, ?how in qlikview?

Mon, 15 Jun 2009 16:22:45 GMT

You need to concatenate the elements together then format the result as a timestamp - see the use of the 'timestamp#' function.

Regards,

Gordon

constructed timestamp, ?how in qlikview?

Mon, 15 Jun 2009 22:14:12 GMT

Heh, that's a good start, but I guess I should've entitled my post "?How do you glue a Date and a Time together to make a Timestamp?

constructed timestamp, ?how in qlikview?

james — Mon, 15 Jun 2009 22:35:36 GMT

I usually use the PEEK statement in a variable during load

In script

Timestamp(Max(timestamp#([vMaxDate, 'YYYY-MM-DD hh:mm:ss'))) AS [TIMESTAMP]

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

constructed timestamp, ?how in qlikview?

Oleg_Troyansky — Mon, 15 Jun 2009 22:37:44 GMT

Use a combination of functions MakeDate() and MakeTime(). Your complete "glue it all" formula would be:

MyDate = MakeDate(MyYear, MyMonth, MyDay) + MakeTime(MyHours, MyMinutes, MySeconds, MyMilliseconds)

Keep in mind that the Date fields in QlikView carry a numeric value and a "formatted" text. The formula above calculates the numeric value (where 1 day = 1), and now you can format it any way, using function Date():

Date = Date(MyDate, 'MM/DD/YYYY hh:mm:ss:fff")

cheers,

Oleg

constructed timestamp, ?how in qlikview?

Mon, 15 Jun 2009 23:13:48 GMT

OK, thanks for the ideas, all.

The dimension ended up being this:

timestamp(date_created_on + MakeTime(hour, minute))

So it's a VB-like combo of Gordon and Oleg's advice. 🙂