Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Kushal_Chawda

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

Kushal_Chawda

or

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

sunny_talwar

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

swuehl
MVP
MVP

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

sunny_talwar

7:00 or 19:00 Stefan?

Not applicable
Author

Sorry, should be 19:00 instead. Thanks!

sunny_talwar

Try this:

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

swuehl
MVP
MVP

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
Author

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?