Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Rounding time

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"

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Rounding time

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;

7 Replies
MVP
MVP

Re: Rounding time

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 ...;


Not applicable

Re: Rounding time

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?

MVP
MVP

Re: Rounding time

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

Not applicable

Re: Rounding time

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

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

MVP
MVP

Re: Rounding time

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;

Not applicable

Re: Rounding time

Im pretty sure you know everything about this program!

Thanks again.

Not applicable

Re: Rounding time

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