Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
abhonsle
Creator
Creator

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
atkinsow
Specialist II
Specialist II

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')

atkinsow
Specialist II
Specialist II

Put this in your script.

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

abhonsle
Creator
Creator
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.

abhonsle
Creator
Creator
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')

atkinsow
Specialist II
Specialist II

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