topic Re: Bucket 8 Hours in Datetime in Load Script in App Development

Bucket 8 Hours in Datetime in Load Script

mramitbhandari17 — Fri, 15 Nov 2024 21:01:27 GMT

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?

Re: Bucket 8 Hours in Datetime in Load Script

marcus_sommer — Thu, 21 Mar 2024 15:29:31 GMT

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.

Re: Bucket 8 Hours in Datetime in Load Script

mramitbhandari17 — Tue, 09 Apr 2024 14:22:44 GMT

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

Re: Bucket 8 Hours in Datetime in Load Script

marcus_sommer — Wed, 10 Apr 2024 05:17:03 GMT

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.