Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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;
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;
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!!
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;