Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I am translating some SQL into QlikView script. In the SQL script exactly ONE hour is subtracted from a time stamp using
dateadd(hour, -1, 'YYYY-MM-DD HH:mm:ss.fff')
I want to do the same in the QV script, but don't know how to. The addmonths won't work, neither will the interval function, as I am not trying to subtract two time stamps from one another, rather a number of hours (integer) from a time stamp.
// Say my original time stamp is 2011-10-03 04:19:17.000
// I want to display XXX as the value 2011-10-03 03:19:17
// Notice that I've dropped the fraction and the hour is one less than the original.
// Here I am forcing QlikView to see the field TS_EVENT as a time value.
let zTS_EVENT = 'time#(TS_EVENT, ' & chr(39) & 'YYYY-MM-DD HH:mm:ss' & chr(39) & ')';
// I've triend all these methods:
TIMES:
load
time(hour($(zTS_EVENT)-1 & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss') as XXX
from ..................
// as well as
TIMES:
load
time(hour($(zTS_EVENT)-time('01', 'HH') & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss') as XXX
from ..................
// as well as
TIMES:
load
time(hour($(zTS_EVENT) & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss') - time('01:00:00', 'HH:mm:ss') as XXX
from ..................
// but none seem to work.
PLEASE HELP!
Thanks!
I came right with this:
if(minute($(zTS_EVENT)) = '00' and second($(zTS_EVENT)) = '00', time((hour($(zTS_EVENT)) & ':00:00' - 1/24), 'HH:mm:ss'),
time(hour($(zTS_EVENT)) & ':00:00', 'HH:mm:ss')) as TS_START,
if(minute($(zTS_EVENT)) = '00' and second($(zTS_EVENT)) = '00', time(hour($(zTS_EVENT)) & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss'),
time((hour($(zTS_EVENT)) & ':00:00' + 1/24), 'HH:mm:ss')) as TS_END,
My if statement’s condition didn’t like the ’00:00’ so I broke it up into separate minutes and seconds
Andries Triegaardt
hei
try this one
timestamp(num(timestamporig)-1/24) as new
Thanks!
I came right with this:
if(minute($(zTS_EVENT)) = '00' and second($(zTS_EVENT)) = '00', time((hour($(zTS_EVENT)) & ':00:00' - 1/24), 'HH:mm:ss'),
time(hour($(zTS_EVENT)) & ':00:00', 'HH:mm:ss')) as TS_START,
if(minute($(zTS_EVENT)) = '00' and second($(zTS_EVENT)) = '00', time(hour($(zTS_EVENT)) & ':' & minute($(zTS_EVENT)) & ':' & second($(zTS_EVENT)), 'HH:mm:ss'),
time((hour($(zTS_EVENT)) & ':00:00' + 1/24), 'HH:mm:ss')) as TS_END,
My if statement’s condition didn’t like the ’00:00’ so I broke it up into separate minutes and seconds
Andries Triegaardt