Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It's possible to convert a Datetime for a Time format greater than 24 hours using Script ?
Ex.: 04/01/1900 05:02:08 -> 101:02
Thanks.
What would be the output for 01/01/1900 11:01:52?
Use Interval() function, will handle your need
Does this look like the right solution?
Script:
Table:
LOAD *,
Interval(Datetime - YearStart(Datetime), 'hh:mm') as Time;
LOAD * Inline [
Datetime
04/01/1900 05:02:08
01/01/1900 11:01:52
];
Doesn't work...
Give me 125 hours...
Shouldn't this be 3 days + 5 hours on the 4th day? and hence a total of 77 hours and 2 minutes?
Hi,
one possible solution could be to format your datetime excel column as Interval when loading in QlikView:
The difference between the 101:02 respective 125:02 interpretation is due to a Leap year bug in Excel that considers 1900 as leap year despite it is not.
Software not sharing this Excel "feature" (e.g. QlikView) presents a date with decimal value 4 as 03/01/1900:
table1:
LOAD Timestamp(Datetime, 'DD/MM/YYYY hh:mm:ss') as Datetime,
Num(Datetime) as decimal,
Interval(Datetime) as Interval
FROM
(ooxml, embedded labels, table is Sheet1);
hope this helps
regards
Marco
Unfortunately No...
04/01/1900 05:02:08 -> 101:02 hours
Then this was working:
Table:
LOAD *,
(Day(Datetime) * 24 + Hour(Datetime)) & ':' & Num(Minute(Datetime), '00') as Time;
LOAD * Inline [
Datetime
04/01/1900 05:02:08
01/01/1900 11:01:52
];