topic Re: Convert Decimal to Seconds in QlikView

Convert Decimal to Seconds

Tue, 11 Mar 2014 15:01:07 GMT

Hi everyone!

I want to convert decimal numbers, in the form of 70.345678901234 (seconds), into mm:ss. I found some solutions in the forums and tried quite a few but still can't figure out what I'm doing wrong. I also tried changing the decimal separator and shortening the numbers to less than 14-digits.

Can you help with that?

Thanks,

Ivelin

Re: Convert Decimal to Seconds

PrashantSangle — Tue, 11 Mar 2014 15:08:54 GMT

Hi,

You can use timestamp()

like this,

=Timestamp(date,'mm:ss')

Re: Convert Decimal to Seconds

Tue, 11 Mar 2014 15:09:01 GMT

Hi, try this:

In your script, in the load statement, write instead of your field:

timestamp(timestamp#(round(Your Field),'mm:ss'),'mm:ss') as New Field

Greetings,

Lucas

EDIT: Fabrice made a very good point, changes are in bold

Re: Convert Decimal to Seconds

Tue, 11 Mar 2014 15:13:45 GMT

Use the time() function and round() also to remove some digits

1 day = 1, 12 hrs= 0,5

so if your field is expressed in seconds, you will need to divide by 3600*24

sth like time(YourField/3600/24, 'hh:mm:ss') as YourFiled

Fabrice

Re: Convert Decimal to Seconds

Anonymous — Tue, 11 Mar 2014 15:17:03 GMT

That would work.

T1:

LOAD * INLINE [

date, responseTime

20140101, 12.72959423502355

20140110, 13.28933262233888

20140120, 99.99893392359389

20140131, 11.18933455334568

];

TEST:

LOAD

date,

'00:00:'&responseTime as Test

Resident T1;

And then format your new field to be represented as Time

Hope this helps

Antoine

Re: Convert Decimal to Seconds

Tue, 11 Mar 2014 15:45:57 GMT

Hi Fabrice,

your solution works best if I keep Replace(responseTime,'.',',') in LOAD.

However, there is a problem with the rounding. It doesn't take into account the first decimal digit and 99.99.. is 1:39 instead of 1:40. Any ideas about that?

Ivelin

Re: Convert Decimal to Seconds

Tue, 11 Mar 2014 16:01:21 GMT

I did not recommend to use replace()

Did you try to use the round() function : time(round(seconds, 1)/24/3600, 'hh:mm:ss')

You are right, the time() function does not round the seconds, it takes the integer part (like floor() function)

Fabrice

Re: Convert Decimal to Seconds

Tue, 11 Mar 2014 16:14:53 GMT

Now it works perfect!

As for replace(), If I don't use it then time() in expressions returns null. That's also the case with Lucas' solution. I guess it's a conflict with the decimal separators..

Ivelin