constructed timestamp, ?how in qlikview?

[#1]

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?

[#2 · Accepted solution]

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

Regards,

Gordon

[#3]

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?

[james]

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

[Oleg_Troyansky]

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

[#6]

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