topic Re: Group data in table by hour in App Development

Group data in table by hour

johnnyjohn — Sat, 16 Nov 2024 18:45:10 GMT

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

Re: Group data in table by hour

BalaBhaskar_Qlik — Wed, 22 Apr 2020 10:53:42 GMT

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;

Re: Group data in table by hour

johnnyjohn — Wed, 22 Apr 2020 11:41:45 GMT

@BalaBhaskar_Qlik 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!

Re: Group data in table by hour

tresB — Wed, 22 Apr 2020 11:59:08 GMT

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