Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Pankaj_Qlik
Partner - Contributor II
Partner - Contributor II

Looping logic in load script to handle overlapping events

I have event related transaction data ordered in ascending format. These events are overlapped based on time frame on each other. 

Requirement: Identify overlapping events in different time frames and calculate the time difference between minimum event_start time and event_end time in one overlapping time frame.

For example:

Events with mc.cproc-0 to mc.cproc-5 are overlapped and we have to consider it as one event so as to calculate difference between start time (2-19-19 11:04 AM) and end time (2-19-19 11:17 AM).

Event_IDEvent_StartEvent_EndEvent_class
mc.cproc-02-19-19 11:04 AM2-19-19 11:06 AMPETROL
mc.cproc-12-19-19 11:05 AM2-19-19 11:06 AMPETROL
mc.cproc-22-19-19 11:06 AM2-19-19 11:07 AMDIESEL
mc.cproc-32-19-19 11:06 AM2-19-19 11:16 AMPETROL
mc.cproc-42-19-19 11:07 AM2-19-19 11:16 AMPETROL
mc.cproc-52-19-19 11:07 AM2-19-19 11:17 AMDIESEL
mc.cproc-62-19-19 8:01 PM2-19-19 10:50 PMPETROL
mc.cproc-72-19-19 8:04 PM2-19-19 10:50 PMDIESEL
mc.cproc-82-19-19 11:06 PM2-19-19 11:16 PMPETROL

 

We need to build up a logic in such a way that these overlapped events can be identified using any counter or common number.

Like, counters can be added as a separate column and then time difference can be calculated using min, max and interval functions.

Expected solution: Build a logic to add one more column 'Counter'.

Event_IDEvent_StartEvent_EndEvent_classCounter
mc.cproc-02-19-19 11:04 AM2-19-19 11:06 AMPETROL1
mc.cproc-12-19-19 11:05 AM2-19-19 11:06 AMPETROL1
mc.cproc-22-19-19 11:06 AM2-19-19 11:07 AMDIESEL1
mc.cproc-32-19-19 11:06 AM2-19-19 11:16 AMPETROL1
mc.cproc-42-19-19 11:07 AM2-19-19 11:16 AMPETROL1
mc.cproc-52-19-19 11:07 AM2-19-19 11:17 AMDIESEL1
mc.cproc-62-19-19 8:01 PM2-19-19 10:50 PMPETROL2
mc.cproc-72-19-19 8:04 PM2-19-19 10:50 PMDIESEL2
mc.cproc-82-19-19 11:06 PM2-19-19 11:16 PMPETROL3

 

Any idea on how to build a logic for this in a load script? 

 

Many Thanks!!

Labels (3)
1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III

maybe try this

Event:
Load Event_ID, Event_Start, Event_End, Event_class,
if(isnull(peek(Event_ID)),1,
if(Event_Start<=peek(end_date_tmp),peek(counter),peek(counter)+1)
) as counter,
if(isnull(peek(Event_ID)),Event_End,
if(Event_Start<=peek(end_date_tmp),rangemax(peek(end_date_tmp),Event_End),Event_End
)) as end_date_tmp

Resident Event1 order by Event_Start;

Resident Event1 order by Event_Start;

 

View solution in original post

4 Replies
asinha1991
Creator III
Creator III

mayble something like this;


Event1:
Load * from Event.qvd(or whatever, as you cannot order by in direct load);

Event:
Load Event_ID, Event_Start, Event_End, Event_class,
if(isnull(peek(Event_ID)),1,
if(Event_Start<=peek(Event_End),peek(counter),peek(counter)+1)
) as counter Resident Event1 order by Event_Start;

Drop table Event1;

Pankaj_Qlik
Partner - Contributor II
Partner - Contributor II
Author

Thanks, I highly appreciate your quick response. I quickly tested your solution and it seems there is one problem; if in case previous end date is smaller than earlier one, it increments the counter; ideally it should not.  Kindly find the result (attached as well as below) that I get when I apply your logic.

Output of your solution:

Event_classEvent_IDEvent_StartEvent_Endcounter
PETROLmc.cproc-01.1c6c524e.019-02-19 11:04:56 AM19-02-19 11:07:44 AM1
PETROLmc.cproc-01.1c6c524e.119-02-19 11:05:16 AM19-02-19 11:06:20 AM1
DIESELmc.cproc-01.1c6c524e.219-02-19 11:06:31 AM19-02-19 11:07:55 AM2
PETROLmc.cproc-01.1c6c524e.319-02-19 11:06:31 AM19-02-19 11:16:14 AM2
PETROLmc.cproc-01.1c6c524e.419-02-19 11:07:01 AM19-02-19 11:16:44 AM2
DIESELmc.cproc-01.1c6c524e.519-02-19 11:07:12 AM19-02-19 11:17:35 AM2
PETROLmc.cproc-01.1c6c524e.619-02-19 8:01:09 PM19-02-19 10:50:46 PM3
DIESELmc.cproc-01.1c6c524e.719-02-19 8:04:31 PM19-02-19 10:50:46 PM3
PETROLmc.cproc-01.1c6c524e.819-02-19 11:06:31 PM19-02-19 11:16:14 PM4

 

Expected solution is :

Event_classEvent_IDEvent_StartEvent_Endcounter
PETROLmc.cproc-01.1c6c524e.019-02-19 11:04:56 AM19-02-19 11:07:44 AM1
PETROLmc.cproc-01.1c6c524e.119-02-19 11:05:16 AM19-02-19 11:06:20 AM1
DIESELmc.cproc-01.1c6c524e.219-02-19 11:06:31 AM19-02-19 11:07:55 AM1
PETROLmc.cproc-01.1c6c524e.319-02-19 11:06:31 AM19-02-19 11:16:14 AM1
PETROLmc.cproc-01.1c6c524e.419-02-19 11:07:01 AM19-02-19 11:16:44 AM1
DIESELmc.cproc-01.1c6c524e.519-02-19 11:07:12 AM19-02-19 11:17:35 AM1
PETROLmc.cproc-01.1c6c524e.619-02-19 8:01:09 PM19-02-19 10:50:46 PM2
DIESELmc.cproc-01.1c6c524e.719-02-19 8:04:31 PM19-02-19 10:50:46 PM2
PETROLmc.cproc-01.1c6c524e.819-02-19 11:06:31 PM19-02-19 11:16:14 PM3

 

Looking for the expected solution. Let me know if you are able to find further any.

Many Thanks!!

asinha1991
Creator III
Creator III

maybe try this

Event:
Load Event_ID, Event_Start, Event_End, Event_class,
if(isnull(peek(Event_ID)),1,
if(Event_Start<=peek(end_date_tmp),peek(counter),peek(counter)+1)
) as counter,
if(isnull(peek(Event_ID)),Event_End,
if(Event_Start<=peek(end_date_tmp),rangemax(peek(end_date_tmp),Event_End),Event_End
)) as end_date_tmp

Resident Event1 order by Event_Start;

Resident Event1 order by Event_Start;

 

Pankaj_Qlik
Partner - Contributor II
Partner - Contributor II
Author

Bravo!! It works perfectly fine..

Many Thanks!!