Looping logic in load script to handle overlapping events

[Pankaj_Qlik]

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_ID	Event_Start	Event_End	Event_class
mc.cproc-0	2-19-19 11:04 AM	2-19-19 11:06 AM	PETROL
mc.cproc-1	2-19-19 11:05 AM	2-19-19 11:06 AM	PETROL
mc.cproc-2	2-19-19 11:06 AM	2-19-19 11:07 AM	DIESEL
mc.cproc-3	2-19-19 11:06 AM	2-19-19 11:16 AM	PETROL
mc.cproc-4	2-19-19 11:07 AM	2-19-19 11:16 AM	PETROL
mc.cproc-5	2-19-19 11:07 AM	2-19-19 11:17 AM	DIESEL
mc.cproc-6	2-19-19 8:01 PM	2-19-19 10:50 PM	PETROL
mc.cproc-7	2-19-19 8:04 PM	2-19-19 10:50 PM	DIESEL
mc.cproc-8	2-19-19 11:06 PM	2-19-19 11:16 PM	PETROL

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_ID	Event_Start	Event_End	Event_class	Counter
mc.cproc-0	2-19-19 11:04 AM	2-19-19 11:06 AM	PETROL	1
mc.cproc-1	2-19-19 11:05 AM	2-19-19 11:06 AM	PETROL	1
mc.cproc-2	2-19-19 11:06 AM	2-19-19 11:07 AM	DIESEL	1
mc.cproc-3	2-19-19 11:06 AM	2-19-19 11:16 AM	PETROL	1
mc.cproc-4	2-19-19 11:07 AM	2-19-19 11:16 AM	PETROL	1
mc.cproc-5	2-19-19 11:07 AM	2-19-19 11:17 AM	DIESEL	1
mc.cproc-6	2-19-19 8:01 PM	2-19-19 10:50 PM	PETROL	2
mc.cproc-7	2-19-19 8:04 PM	2-19-19 10:50 PM	DIESEL	2
mc.cproc-8	2-19-19 11:06 PM	2-19-19 11:16 PM	PETROL	3

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

Many Thanks!!

[asinha1991]

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]

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_class	Event_ID	Event_Start	Event_End	counter
PETROL	mc.cproc-01.1c6c524e.0	19-02-19 11:04:56 AM	19-02-19 11:07:44 AM	1
PETROL	mc.cproc-01.1c6c524e.1	19-02-19 11:05:16 AM	19-02-19 11:06:20 AM	1
DIESEL	mc.cproc-01.1c6c524e.2	19-02-19 11:06:31 AM	19-02-19 11:07:55 AM	2
PETROL	mc.cproc-01.1c6c524e.3	19-02-19 11:06:31 AM	19-02-19 11:16:14 AM	2
PETROL	mc.cproc-01.1c6c524e.4	19-02-19 11:07:01 AM	19-02-19 11:16:44 AM	2
DIESEL	mc.cproc-01.1c6c524e.5	19-02-19 11:07:12 AM	19-02-19 11:17:35 AM	2
PETROL	mc.cproc-01.1c6c524e.6	19-02-19 8:01:09 PM	19-02-19 10:50:46 PM	3
DIESEL	mc.cproc-01.1c6c524e.7	19-02-19 8:04:31 PM	19-02-19 10:50:46 PM	3
PETROL	mc.cproc-01.1c6c524e.8	19-02-19 11:06:31 PM	19-02-19 11:16:14 PM	4

Expected solution is :

Event_class	Event_ID	Event_Start	Event_End	counter
PETROL	mc.cproc-01.1c6c524e.0	19-02-19 11:04:56 AM	19-02-19 11:07:44 AM	1
PETROL	mc.cproc-01.1c6c524e.1	19-02-19 11:05:16 AM	19-02-19 11:06:20 AM	1
DIESEL	mc.cproc-01.1c6c524e.2	19-02-19 11:06:31 AM	19-02-19 11:07:55 AM	1
PETROL	mc.cproc-01.1c6c524e.3	19-02-19 11:06:31 AM	19-02-19 11:16:14 AM	1
PETROL	mc.cproc-01.1c6c524e.4	19-02-19 11:07:01 AM	19-02-19 11:16:44 AM	1
DIESEL	mc.cproc-01.1c6c524e.5	19-02-19 11:07:12 AM	19-02-19 11:17:35 AM	1
PETROL	mc.cproc-01.1c6c524e.6	19-02-19 8:01:09 PM	19-02-19 10:50:46 PM	2
DIESEL	mc.cproc-01.1c6c524e.7	19-02-19 8:04:31 PM	19-02-19 10:50:46 PM	2
PETROL	mc.cproc-01.1c6c524e.8	19-02-19 11:06:31 PM	19-02-19 11:16:14 PM	3

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

Many Thanks!!

[asinha1991 · Accepted solution]

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]

Bravo!! It works perfectly fine..

Many Thanks!!