Bucket 8 Hours in Datetime in Load Script

[mramitbhandari17]

Hi,

I would like to know that to get datetime data in 8 hours bucket under 3 groups (like 0:00 to 08:00 a.m., 8:01 a.m. to 16:00 pm, 16:01 p.m to 23:59.)

Extraction from Elastic Kibana. into Qliksense app and number of rows are in billions. Event per second are almost 1500 eps.

Fastest way to fetch such information in hour bucket.

Is it possible in Master calendar or Qlik Script SQL query?

[marcus_sommer]

Maybe just with: ceil(hour(YourTimeField) / 3) will return a flag with values of 1- 3 and wrapping with a pick() you could assign any wanted number- and/or string-values to it.

Within a calendar it's rather not suitable else better would be to have an extra time-table which is even more simpler to create as a calendar, like:

load second(Time) as Second, ...;
load time(recno() / 86400) as Time autogenerate 86400;

Further you should split your timestamps into dates and times which could be separated per floor() and frac() to reduce the number distinct field-values.

[mramitbhandari17]

Hi,

Is it possible to get Interval 3 hours for weekly data in Qlik script query editor with date?

As we have received more than 4.14 billion data from elastic ODBC connector.

Please assist

[marcus_sommer]

You may just add: ceil(hour(Time) / 3) as HourBucket within the time-table.

Beside this I suggest to consider incremental approaches by loading such data-sets.