topic Re: Round up time to the start of hour in QlikView

Round up time to the start of hour

Wed, 13 Apr 2016 05:54:43 GMT

How to round up time to the start of hour? Say,

Time Return

03:05 AM 03:00

07:45 PM 19:00

I want to know whether it's attainable by Set Analysis or by Data Load. Thanks!

Re: Round up time to the start of hour

Kushal_Chawda — Wed, 13 Apr 2016 09:22:31 GMT

=time(hour(time#(TimeField,'hh:mm TT')),'hh:mm') as TIME

Re: Round up time to the start of hour

Kushal_Chawda — Wed, 13 Apr 2016 09:27:09 GMT

hour(time#(TimeField,'hh:mm TT'))&':'&00 as TIME

Re: Round up time to the start of hour

sunny_talwar — Wed, 13 Apr 2016 09:54:08 GMT

May be this:

Time(Round(Time, 1/24), 'hh:mm') as Time

Time(Floor(Time, 1/24), 'hh:mm') as Time

Althouth I am not sure how you converted 7:45 PM to 17:00 -> did you mean 20:00 or 19:00 intead? First expression will get you 20:00 and second will get you 19:00

Re: Round up time to the start of hour

swuehl — Wed, 13 Apr 2016 09:59:52 GMT

Try

Set TimeFormat = 'hh:mm TT';

LOAD

Time as TimeOriginal,

Time(Floor(Time, 1/24), 'hh:mm') as Time, // adapt the time format as needed, if you want to show AM/PM

...

FROM ...;

edit: this will of course perform a rounding to the lower hour value 07:45 --> 07:00

Re: Round up time to the start of hour

sunny_talwar — Wed, 13 Apr 2016 10:02:12 GMT

7:00 or 19:00 Stefan?

Re: Round up time to the start of hour

Wed, 13 Apr 2016 10:03:36 GMT

Sorry, should be 19:00 instead. Thanks!

Re: Round up time to the start of hour

sunny_talwar — Wed, 13 Apr 2016 11:08:13 GMT

Try this:

Time(Floor(Time, 1/24), 'hh:mm') as Time

Re: Round up time to the start of hour

swuehl — Wed, 13 Apr 2016 11:12:04 GMT

Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

7:00 or 19:00 Stefan?

19:00. Missed the PM on the second line

07:45 PM --> 19:00

May, if the above doesn't work, please check that your input records are correctly read in as time, having a numeric representation, e.g. by using Time#() interpretation function or setting the default format.

See also following, also applies to time values:

Why don’t my dates work?

Re: Round up time to the start of hour

Wed, 13 Apr 2016 22:43:47 GMT

Thanks, Sunny! You are right that it returns the rounded up time value but why are there duplicates?

I am intending this to use as a dimension, I expect that the measures will be aggregated according to the unique time value.

Any idea why it behaved like that?

Re: Round up time to the start of hour

sunny_talwar — Thu, 14 Apr 2016 09:04:33 GMT

I there date information attached to it for some reason?

May be give this a shot:

Time(Floor(Frac(Time), 1/24), 'hh:mm') as Time