Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

Contributor II
Contributor II

Hi Henric,

thanks for your reply and your great solution.

Regards
Stefan