topic Re: Convert Decimal into hh:mm:ss in QlikView

Convert Decimal into hh:mm:ss

Anonymous — Fri, 08 Sep 2017 13:00:57 GMT

Hello all,

I want to convert decimal values into hh:mm:ss.

PFA excel file.

Can someone help me how to achieve this?

Thanks,

Nisha

Re: Convert Decimal into hh:mm:ss

Kushal_Chawda — Fri, 08 Sep 2017 13:03:09 GMT

Are these values in seconds?

Re: Convert Decimal into hh:mm:ss

Peter_Cammaert — Fri, 08 Sep 2017 13:09:57 GMT

QlikView Date & Time functions use a decimal value to represent timestamps, just like in Excel: the integer part represents the date, the fraction is the time as part of a day (ex. 0.25 = 6AM). If you put those values straight into a call to the Time() function (or even better, Interval()), you will get a value in hh:mm:ss.

But just like Kushal asked before, I would like to know too what those values really represent.

Re: Convert Decimal into hh:mm:ss

its_anandrjs — Fri, 08 Sep 2017 13:10:13 GMT

What is the actual format for this decimal data please elaborate.

Re: Convert Decimal into hh:mm:ss

Anonymous — Fri, 08 Sep 2017 13:17:40 GMT

These values are in hh:mm:ss.

Re: Convert Decimal into hh:mm:ss

Kushal_Chawda — Fri, 08 Sep 2017 13:20:15 GMT

I mean data in your excel file is in seconds?

Re: Convert Decimal into hh:mm:ss

Anonymous — Fri, 08 Sep 2017 13:20:39 GMT

Hi All,

These values are in hh:mm:ss.

Thanks

Re: Convert Decimal into hh:mm:ss

Peter_Cammaert — Fri, 08 Sep 2017 13:21:41 GMT

No they are not. They are just decimals, even in Excel which doesn't format them otherwise. What do they actually represent?

For example, if I format the very last value in Excel into a DateTime value, I get 19/11/6944 10:04:48. Is that correct?

Re: Convert Decimal into hh:mm:ss

Peter_Cammaert — Fri, 08 Sep 2017 13:23:35 GMT

If you don't know the answer to this question, maybe you can tell us where your values come from?

Re: Convert Decimal into hh:mm:ss

rohitraut — Fri, 08 Sep 2017 13:27:45 GMT

Hello Nisha,

Try this,

time(date#( DecimalValue ,'hhmmss'),'hh:mm:ss') as time

Hope this help!

Re: Convert Decimal into hh:mm:ss

its_anandrjs — Fri, 08 Sep 2017 13:33:04 GMT

It not seems the values are in proper format

Ex:-

1. Today date is 08/09/2017 if represent this date into NUM (Numbers) it will be 42986 and it is again converted into any date formats Ex:- ddmmyyyy, YYYYMMDD etc

2. This months monthend date numeric value is 43008.999999988 which is 30/09/2017

Re: Convert Decimal into hh:mm:ss

Anonymous — Fri, 08 Sep 2017 13:34:23 GMT

I am checking with Data team regarding the format of decimal values. I think they just took time stamp from Date field and converted into Decimals.

For ex: 1.5 Decimal value should convert into 1:30:00.

Thanks,

Nisha

Re: Convert Decimal into hh:mm:ss

Kushal_Chawda — Fri, 08 Sep 2017 13:39:07 GMT

That means decimal values are in hour, below should work

=interval(YourField/24,'hh:mm:ss') as TimeValue

Re: Convert Decimal into hh:mm:ss

rohitraut — Fri, 08 Sep 2017 13:46:27 GMT

Hello Nisha,

Try this,

time(alt( date#( DecimalValue , 'yyyyddmm hhmmss' ),

date#( DecimalValue , 'hhmmss' ),

date#( DecimalValue , 'hh:mm.ss' ),

date#( DecimalValue , 'ss' ),

date#( DecimalValue , 'm.ss' ),

'hh:mm:ss') ) as DecimalValue1

Please find the .qvw.

Hope this help.

Re: Convert Decimal into hh:mm:ss

effinty2112 — Fri, 08 Sep 2017 13:52:47 GMT

Hi Nisha,

If the values are in days then

=Interval(DecimalValue,'hh:mm:ss')

That seems okay for some of your values

0.53 => 12:43:12

When we go over a DecimalValue of 1 then should consider changing your format

=Interval(DecimalValue,'D hh:mm:ss')

2.17 => 2 04:04:48

I would be nice to insert the letter 'day' into the format but that just messes things up:

Interval(2.17,'D day hh:mm:ss') = 2 2a0 04:04:48

you can work around this:

Replace(Interval(2.17,'D $ hh:mm:ss'),'$','days') and get 2 days 04:04:48

You can really go for it:

=Replace(Replace(Replace(Replace(Interval(2.17,'D $ hh£mm#ss@'),'$','days'),'£','hrs '),'#','min '),'@',' sec')

returns

2 days 04hrs 04min 48 sec

There are some really big values in your set, the largest is 1842608.42

1842608 days 10hrs 04min 48 sec

If the values are not days then you'll need to divide your argument by the appropriate quotient. If in seconds then divide by 86400 to get

21 days 07hrs 50min 08 sec for your largest value.

Regards

Andrew

Re: Convert Decimal into hh:mm:ss

Anonymous — Fri, 08 Sep 2017 15:13:38 GMT

Hello All,

Sorry for the confusion. Those decimal values are in Minutes.

Hope this helps.

Thanks Nisha

Re: Convert Decimal into hh:mm:ss

Peter_Cammaert — Fri, 08 Sep 2017 15:17:44 GMT

No problem, now it's clear. Let's try again. How about:

=Interval(DecimalValue/(24*60), 'hh:mm:ss')

The call to Interval() will make sure that very big values will still be converted to a massive amount of hours...For example, this application of the same expression on the last value in your Excel file:

=Interval(1842608.42/(24*60), 'hh:mm:ss')

will produce

30710:08:25

If you want different formatting, check Andrews post which contains a lot of useful advice.

[Edit] Sorry for the spam, post contained a lot of grammar and spelling mistakes. Time to go home

Re: Convert Decimal into hh:mm:ss

effinty2112 — Fri, 08 Sep 2017 15:23:28 GMT

Hi Nisha,

Your largest value comes out as:

=Replace(Replace(Replace(Replace(Interval(1842608.42/1440,'D $ hh£mm#ss@'),'$','days'),'£','hrs '),'#','min '),'@',' sec') =

1279 days 14hrs 08min 25 sec

=Interval(1842608.42/1440,'hh:mm:ss')=

30710:08:25

Regards

Andrew