Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
PrashantSangle

Hi,

You can use timestamp()

like this,

=Timestamp(date,'mm:ss')

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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
Author

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

Anonymous
Not applicable
Author

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
Author

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
Author

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
Author

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