Convert time string an round time

[Anonymous]

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

[#2]

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

[hic]

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

[Anonymous]

Hi Henric,

thanks for your reply and your great solution.

Regards
Stefan