Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
stefan_fischer
Contributor II
Contributor II

Convert time string an round time

Hi,

i will convert date and time in the right format.

strTime: 10.02.2014 07:45:50

10.02.2014 -> Date(Left(strTime,10),'DD.MM.YYYY') AS DAT,

07:45:50 -> Time#(Right(strTime,8),'HH.MM.SS') AS TIM

or

07:45:50 -> Time(Right(strTime,8)) as TIM

Now, strTime should be round to next 10 min, e.g. 07:50:00. I've check a formula in excel, but in QlikView it's no working.

07:50:00 -> RUNDEN(TIM*24/(1/6);0)*(1/6)/24

Do you have any idea, whats wrong?

Regards
Stefan

3 Replies
Not applicable

Hi Stefan

I'd rather use Floor() and Frac() to extract day and time part from the timestamp.

When using Frac() you can specify nearest number to round to. 1 / 24 / 6 is going to round "roughly" to closest 10 minutes. By "roughly" I mean rounding errors described here community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors

Lukasz

Henric_Cronström

I would not use string functions for this. Hence, I would use

Date(Floor(TimeStamp#(strTime,'DD.MM.YYYY hh:mm:ss')),'DD.MM.YYYY') AS Date,

Time(Frac(TimeStamp#(strTime,'DD.MM.YYYY hh:mm:ss')),'hh:mm:ss') AS Time,  // (seconds)

Time(Ceil(Frac(TimeStamp#(strTime,'DD.MM.YYYY hh:mm:ss')),10/24/60),'hh:mm') AS Time10m, // (10-min interval)

HIC

stefan_fischer
Contributor II
Contributor II
Author

Hi Henric,

thanks for your reply and your great solution.

Regards
Stefan