Group data in table by hour

[johnnyjohn]

I have the following data

I would like to create a bar chart that group the timestamp by hour and sums the qty_traded in that hour. Currently I just display the data from the table above raw (see below), and I am not sure how to achieve this grouping by hour, so that I have much less bars.

Help would be greatly appreciated. 

Thanks

[balabhaskarqlik]

Try this:

Table1:
Load
time,
Left(time,2) as Timestamp,
qty_traded
from abc;

noconcatenate

Table2:
Load
Timestamp,
Sum(qty_traded) as total_qty
Resident Table1
group by Timestamp;

Drop table Table1;

[johnnyjohn]

@balabhaskarqlik thank you for your reply.

I had thought there would be a general solution to do that for different time intervals, but using this method it seems not.

Can this be done for half hours intervals, e.g. 09:00, 09:30, 10:00, 10:30?

Thanks!

[tresesco]

You could use class() in calculated dimension expression (or in script) like:

=Time(SubField(replace(Class(Time, 30/(24*60)), '<= x <','|'), '|',1))
& '-' &
Time(SubField(replace(Class(Time, 30/(24*60)), '<= x <','|'), '|',2))