Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Henric,
thanks for your reply and your great solution.
Regards
Stefan