Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rsvebeck
Valued Contributor

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

1 Solution

Accepted Solutions
MVP
MVP

Round to closest half hour?

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

7 Replies
MVP
MVP

Round to closest half hour?

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
Valued Contributor

Round to closest half hour?

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

Not applicable

Re: Round to closest half hour?

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
Valued Contributor

Re: Round to closest half hour?

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

Regards

Robert

Not applicable

Re: Round to closest half hour?

Thanks! Was thinking too dificult...

Re: Round to closest half hour?

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

daveamz01
Contributor III

Re: Round to closest half hour?

I used class() function:

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

Regards,

David

Community Browser