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.

Not applicable

Sorting events into timeslots

Hallo,

I load some million events from a psql database. The events occures over the day at any time and the entity contains a datetime-field. I want to count the events occuring at different times and days of a week, aggregated over some years.

Therefore i want to divide the 10080 minutes of a week in a numberd sequence of 10080/n slots, numbered from 1 to 10080/n. Example: MinutesOfTimeslot=15, timeslot nr.1 goes from 00:00:00 to 00:14:59 on monday an the last nr.672 from 23:45:00 to 23:59:59 on sunday.

I think to do the calculation during the load-process like this:

ODBC CONNECT TO PostgreSQL64;

LOAD "eventTime",

slot(enventTime,MinutesOfTimeslot) as slot_nr;

SQL SELECT *

FROM "events";

I am not very experienced in SQL or QLIKVIEW and don't know if there are any existing functions fullfilling my wishes. Later it could be interesting to consider vacation and public holidays to compare similar days or weeks over the years.

Any ideas?

Tags (2)
4 Replies

Re: Sorting events into timeslots

Can you post the data for MinutesOfTimeslot

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: Sorting events into timeslots

MinutesOfTimeslot is a parameter for the length in minutes of a timeslot.When i choose this equal to 15, i will have 672 timslots in a week, with 10 - 1008 timeslots ...

Re: Sorting events into timeslots

Hi,

one solution might be:

QlikCommunity_Thread_230676_Pic1.JPG

QlikCommunity_Thread_230676_Pic2.JPG

QlikCommunity_Thread_230676_Pic4.JPG

QlikCommunity_Thread_230676_Pic3.JPG

tabEvents:

LOAD *,

    Interval(Floor(eventTimestamp-WeekStart(eventTimestamp),'00:01'),'d hh:mm') as eventTime;

LOAD RecNo() as eventID,

    Timestamp(Now()-Rand()*1000) as eventTimestamp

AutoGenerate 1000;

tabEventTime:

LOAD Distinct

    eventTime,

    IterNo() as MinutesOfTimeslot,

    Ceil(eventTime*1440/IterNo()) as slot_nr,

    Interval(Floor(eventTime,IterNo()/1440),'d hh:mm') as slot_time

Resident tabEvents

While IterNo() <= 1440;

hope this helps

regards

Marco

Not applicable

Re: Sorting events into timeslots

Hi,

thank you.

I am working with a free desktop version. Therefor i cannot read your example.

So i first tried to understand the code. After that i implemented, based on your example a solution. This works, but i saw some results i don't really understand in the moment, and i have to look for. It seems, there are some problems with the format of my event-timestamp and the functions ceil and floor returns values i cannot comprehend.

Furthermore i recognized, that my solution works, but is very slow.

Regards

Ruediger