Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Group hours

Hi All,

I have a "Date" field in the format  eg: "3/24/2017  1:05:00 AM". I used Time([Date], 'h tt') to get 1 AM, 2 AM , 3 AM etc.

My problem is I get the data in the below format

I would like to group these to show only

01:00 AM

02:00 AM

03:00 AM

Need help in achieving this   

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

You could either just go into the Number format for the field in the UI and select Timestamd and type

h[.fff] TT as the Format Pattern.

Or you could do it in the script like this:

time(MakeTime(hour(Time(frac(Date))),0,0),'h TT') as GroupedHour

View solution in original post

10 Replies
hectormunoz
Contributor III
Contributor III

use hour(field) to extract the numeric value. Time() function just formats the data, but still has the original value.

rgds

Digvijay_Singh

Like this may be -

Capture.PNG

hectormunoz
Contributor III
Contributor III

As the timestamp fields are numbers, you can use floor(field) to retrieve only the day value, and time(frac(field)) to extract the time part. The format will depend on the second parameter.

date(floor(field)) will set to 00:00:00 the time part

time(frac(Date),'hh:mm') will keep the second precision, but will not show in the field

vishsaggi
Champion III
Champion III

May be this?

= Time(Date(yourdatefield, 'MM/DD/YYYY hh:mm:ss TTT'), 'hh:mm TTT')

Anonymous
Not applicable
Author

Put this in your script.

=MakeTime(hour(Time(frac(Date))),0,0) as GroupedHour

Anonymous
Not applicable
Author

Hi,

I think I will elaborate my question with a actual example. I have the below data in my date field

When I use the hour function on the above. I get the data as

What I would like to see is just one time 09:00 AM. Basically for the Range -> 09:00 - 09:59 show it as 09:00

Hope you understood my question.

Anonymous
Not applicable
Author

Thank you Wallo. This is what I am looking for. How do you remove the minutes and the seconds  and show

09 AM

10 AM

12 AM etc.....

hectormunoz
Contributor III
Contributor III

time(MakeTime(hour(Time(frac(Date))),0,0) , 'HH TT')

Anonymous
Not applicable
Author

You could either just go into the Number format for the field in the UI and select Timestamd and type

h[.fff] TT as the Format Pattern.

Or you could do it in the script like this:

time(MakeTime(hour(Time(frac(Date))),0,0),'h TT') as GroupedHour