topic Re: Transform a Datetime in Time greater than 24 Hours in QlikView

Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 18:54:50 GMT

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.

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 19:01:25 GMT

Hi,

try this to convert to hh:mm

load ...

floor(yourdatefield*24) & ':' & floor(yourdatefield*24*60-floor(yourdatefield*24)*60) as yourNEWtimefield,

...

from...

Hope it helps

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 19:01:28 GMT

How is the number getting converted to 101:02?

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 19:09:40 GMT

Hi,

This formula give me 125 hours...

04/01/1900 05:02:08

It's a Excel Time...

4 Days + 05:02:08 Hours... = 101:02

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 19:10:22 GMT

Hi Sunny,

It's a Excel Time Format...

4 Days + 05:02 ...

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 19:11:11 GMT

Try this:

Table:

LOAD *,

(Day(Datetime) * 24 + Hour(Datetime)) & ':' & Minute(Datetime) as Time;

LOAD * Inline [

Datetime

04/01/1900 05:02:08

];

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 19:12:21 GMT

Slight update:

Table:

LOAD *,

(Day(Datetime) * 24 + Hour(Datetime)) & ':' & Num(Minute(Datetime), '00') as Time;

LOAD * Inline [

Datetime

04/01/1900 05:02:08

];

Re: Transform a Datetime in Time greater than 24 Hours

MarcoWedel — Mon, 09 May 2016 19:22:07 GMT

Interval()

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 20:07:13 GMT

Thanks!

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 20:12:46 GMT

The correct is 101:02

And if i have a date like this:

01/01/1900 11:01:52

This function doesn't work...

It's a hard problem with format value in Excel...

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 20:18:30 GMT

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

Re: Transform a Datetime in Time greater than 24 Hours

Clever_Anjos — Mon, 09 May 2016 20:24:22 GMT

Use Interval() function, will handle your need

Interval ‒ QlikView

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 20:25:59 GMT

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

];

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 20:26:47 GMT

Doesn't work...

Give me 125 hours...

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 20:26:57 GMT

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

Re: Transform a Datetime in Time greater than 24 Hours

MarcoWedel — Mon, 09 May 2016 20:27:30 GMT

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

Re: Transform a Datetime in Time greater than 24 Hours

Anonymous — Mon, 09 May 2016 20:30:05 GMT

Unfortunately No...

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

Re: Transform a Datetime in Time greater than 24 Hours

sunny_talwar — Mon, 09 May 2016 20:32:28 GMT

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

];