topic Re: Convert time string an round time in QlikView

Convert time string an round time

Anonymous — Mon, 24 Feb 2014 10:34:31 GMT

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

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

Re: Convert time string an round time

Mon, 24 Feb 2014 11:00:57 GMT

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

Re: Convert time string an round time

hic — Mon, 24 Feb 2014 11:07:26 GMT

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

Re: Convert time string an round time

Anonymous — Mon, 24 Feb 2014 12:07:16 GMT

Hi Henric,

thanks for your reply and your great solution.

Regards
Stefan