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.
Not applicable

Round up time to the start of hour

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!

10 Replies

Re: Round up time to the start of hour

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

Re: Round up time to the start of hour

or

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

Re: Round up time to the start of hour

May be this:

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

or

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

MVP
MVP

Re: Round up time to the start of hour

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

7:00 or 19:00 Stefan?

Not applicable

Re: Round up time to the start of hour

Sorry, should be 19:00 instead. Thanks!

Re: Round up time to the start of hour

Try this:

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

MVP
MVP

Re: Round up time to the start of hour

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?



Not applicable

Re: Round up time to the start of hour

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?

Community Browser