Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Convert Decimal to Seconds

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

1 Solution

Accepted Solutions
Not applicable

Re: Convert Decimal to Seconds

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

View solution in original post

7 Replies

Re: Convert Decimal to Seconds

Hi,

You can use timestamp()

like this,

=Timestamp(date,'mm:ss')

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Convert Decimal to Seconds

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

Not applicable

Re: Convert Decimal to Seconds

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

View solution in original post

Partner
Partner

Re: Convert Decimal to Seconds

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

Not applicable

Re: Convert Decimal to Seconds

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

Not applicable

Re: Convert Decimal to Seconds

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

Not applicable

Re: Convert Decimal to Seconds

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