Transform a Datetime in Time greater than 24 Hours

[Anonymous]

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.

[sunny_talwar]

What would be the output for 01/01/1900 11:01:52?

[Clever_Anjos]

Use Interval() function, will handle your need

Interval ‒ QlikView

[sunny_talwar]

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

];

[Anonymous]

Doesn't work...

Give me 125 hours...

[sunny_talwar]

Shouldn't this be 3 days + 5 hours on the 4th day? and hence a total of 77 hours and 2 minutes?

[MarcoWedel · Accepted solution]

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

[Anonymous]

Unfortunately No...

04/01/1900 05:02:08 -> 101:02 hours

[sunny_talwar]

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

];