topic Re: Group hours in QlikView

Group hours

Anonymous — Thu, 04 May 2017 19:37:20 GMT

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

Re: Group hours

hectormunoz — Thu, 04 May 2017 19:58:03 GMT

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

rgds

Re: Group hours

Digvijay_Singh — Thu, 04 May 2017 20:02:24 GMT

Like this may be -

Re: Group hours

hectormunoz — Thu, 04 May 2017 20:10:16 GMT

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

Re: Group hours

vishsaggi — Thu, 04 May 2017 20:13:02 GMT

May be this?

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

Re: Group hours

Anonymous — Thu, 04 May 2017 20:56:12 GMT

Put this in your script.

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

Re: Group hours

Anonymous — Thu, 04 May 2017 20:57:10 GMT

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.

Re: Group hours

Anonymous — Thu, 04 May 2017 21:01:40 GMT

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

Re: Group hours

hectormunoz — Thu, 04 May 2017 21:03:07 GMT

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

Re: Group hours

Anonymous — Thu, 04 May 2017 21:06:30 GMT

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

Re: Group hours

Anonymous — Thu, 04 May 2017 21:09:28 GMT

Superb!. Thank you so much