Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
You need to concatenate the elements together then format the result as a timestamp - see the use of the 'timestamp#' function.
Regards,
Gordon
You need to concatenate the elements together then format the result as a timestamp - see the use of the 'timestamp#' function.
Regards,
Gordon
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?
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');
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
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. 🙂