topic Rounding time in QlikView

Rounding time

Mon, 30 Jul 2012 15:53:15 GMT

I need some help on rounding. I have some values that I want to round either to the closest hour or closest half hour.

for example: (This is a 24 hour clock, not 12)

00.28.26 should be 00.30.00

07.58.26 should be 08.00.00

19.58.26 should be 20.00.00

20.29.00 should be 20.30.00

Any idea how to go about this?

My field with the time data is called "Metrics Time"

Re: Rounding time

swuehl — Mon, 30 Jul 2012 16:01:29 GMT

I assumed you have set your time format accordingly and your field is interpreted as time value.

Try

LOAD

[Metrics Time],

time(round([Metrics Time],maketime(0,30))) as RoundedMetricsTime,

...

FROM ...;

Re: Rounding time

Mon, 30 Jul 2012 17:25:58 GMT

Swuehl to the rescue! As always!

That function loads but the new field doesn't have any data.

Perhaps I haven't done the correct formatting?

Re: Rounding time

swuehl — Mon, 30 Jul 2012 17:31:50 GMT

If you haven't set the correct standard time format, your field is probably not correctly interpreted, try

Try

LOAD *,

time(round([Metrics Time],maketime(0,30))) as RoundedMetricsTime;

LOAD

time#([Metrics Time],'hh.mm.ss') as [Metrics Time]

...

FROM ...;

If you are coping with intervals rather than time values (i.e. values can exceed 24h), use the interval#() / interval() functions.

Hope this helps,

Stefan

Re: Rounding time

Mon, 30 Jul 2012 17:44:32 GMT

I guess im kind of confused as to where im putting all this...

You'd think I would have gotten it by now.

Re: Rounding time

swuehl — Mon, 30 Jul 2012 17:51:39 GMT

If above is your general format you want to use as time format, use

SET TimeFormat='hh.mm.ss';

at the top of your script. If so, I would recommend to use the standard format.

If you interpret a time format using time#() function, this will only affect the field you are applying this function to ([Metrics Time2] in your example). So if you use TIME again in the calculation for the rounded value, the format is not interpreted with the 'special' format code. You can do a preceding load like I've shown above or just embed the format interpretation into the round:

time(round( time#([Time],'hh.mm.ss'),maketime(0,30))) as RoundedMetricsTime;

Re: Rounding time

Mon, 30 Jul 2012 18:04:56 GMT

Im pretty sure you know everything about this program!

Thanks again.

Re: Rounding time

Fri, 07 Feb 2014 11:32:22 GMT

Hey guys,

to round the time is also very important to show the correct interval value in the format hh:mm:ss. I've had the problem, that QV shows the floor-value of an interval.

e.g.:

0,00024722223 = 00:05:00

0,00024722221 should be rounded to 00:05:00, but QV shows 00:04:59

By using the expression round(value,maketime(0,0,1)), QV rounds the value to the nearest full second. In my example it is 00:05:00.

Bye,

Riffi