Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
RSvebeck
Specialist
Specialist

Round to closest half hour?

Which is the most simple way to (in the load script) to do a rounding of a timestamp field (hours:minutes) into "segmensts" like this:


Origin Rounded
11:39  11:30
01:34  01:30
03:11  03:00
05:43  05:30
03:27  03:30
06:16  06:30
03:54  04:00


Regards


Robert Svebeck

Svebeck Consulting AB
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Fortunately, round() takes as base as parameter instead of decimal places, so you could do it like

Timestamp(round(Time, (1/48))) as RoundedTime;

Hope this helps,

Stefan

View solution in original post

7 Replies
swuehl
MVP
MVP

Fortunately, round() takes as base as parameter instead of decimal places, so you could do it like

Timestamp(round(Time, (1/48))) as RoundedTime;

Hope this helps,

Stefan

RSvebeck
Specialist
Specialist
Author

Great! It works, and I doubt that it can be done in a more simple way. 🙂

Svebeck Consulting AB
Not applicable

This indeed works perfect for rounding to half hours. Maybe a very simple question, but what is the calculation for rounding to full hours?

RSvebeck
Specialist
Specialist
Author

Rounding to closest hour: Timestamp(round(Time, (1/24))) as RoundedTime;

Regards

Robert

Svebeck Consulting AB
Not applicable

Thanks! Was thinking too dificult...

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

A word of caution about doing your own arithmetic with times.

round(time('02:45'), 'hh:mm') = time('03:00', 'hh:mm') AND MakeTime(3)

but

round(time('01:45'), 'hh:mm') <> time('02:00', 'hh:mm') OR MakeTime(2)

Due to numeric precision, calculated times may not align with time generated by QV time functions or database times. Not a problem if all your times are passing through the same arithmetic, but a potentially a subtle bug if they are not.

See

http://qlikviewnotes.blogspot.com/2011/10/correct-time-arithmetic.html

-Rob

http://robwunderlich.com

daveamz
Partner - Creator III
Partner - Creator III

I used class() function:

time(class(timestamp#(timestamp,'M/D/YYYY h:mm:ss TT'),MakeTime(0,30)))

Regards,

David