Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

abhonsle
Contributor

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
Valued Contributor II

Re: Group hours

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

10 Replies
hectormunoz
New Contributor II

Re: Group hours

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

rgds

Digvijay_Singh
Honored Contributor III

Re: Group hours

Like this may be -

Capture.PNG

hectormunoz
New Contributor II

Re: Group hours

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
Esteemed Contributor III

Re: Group hours

May be this?

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

atkinsow
Valued Contributor II

Re: Group hours

Put this in your script.

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

abhonsle
Contributor

Re: Group hours

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
Contributor

Re: Group hours

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
New Contributor II

Re: Group hours

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

atkinsow
Valued Contributor II

Re: Group hours

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