Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a data table that contains set of events (Enter and Exit) and corresponding date stamps for each one of them.
This is an example of RAW data:
Event Date Time | Event Date | ID | Event |
1/1/10 5:12 AM | 1/1/2010 | 3250536 | Enter |
1/1/10 5:23 AM | 1/1/2010 | 3250531 | Exit |
1/1/10 5:38 AM | 1/1/2010 | 3250289 | Enter |
1/1/10 6:08 AM | 1/1/2010 | 3250490 | Enter |
1/1/10 6:15 AM | 1/1/2010 | 3250536 | Exit |
1/1/10 6:40 AM | 1/1/2010 | 3250628 | Exit |
1/1/10 7:15 AM | 1/1/2010 | 3250490 | Exit |
1/1/10 7:52 AM | 1/1/2010 | 3250605 | Enter |
1/1/10 8:25 AM | 1/1/2010 | 3250289 | Exit |
1/1/10 8:50 AM | 1/1/2010 | 3250289 | Enter |
1/1/10 8:54 AM | 1/1/2010 | 3250289 | Exit |
1/1/10 12:09 PM | 1/1/2010 | 3250289 | Enter |
Each ID handles at least one pair of events per day (for example for ID = 3215028):
11/19/10 5:48 AM | 11/19/2010 | 3215028 | Enter |
11/19/10 4:38 PM | 11/19/2010 | 3215028 | Exit |
11/20/10 5:44 AM | 11/20/2010 | 3215028 | Enter |
11/20/10 12:07 PM | 11/20/2010 | 3215028 | Exit |
11/22/10 5:44 AM | 11/22/2010 | 3215028 | Enter |
11/22/10 5:42 PM | 11/22/2010 | 3215028 | Exit |
I need to calculate the difference between Exit and Enter event per ID per period of time.
I was trying to group these records together during the data load, but was unable to do so.
What's the best way to do it? I would appreciate an expert suggestion.
Best regards,
VK
Hi,
Hope i have understood your question.
Pl look at the attached application.
In my application i have seggregated "Event Date Time" into two time like Enter_Time and Exit_Time using Peek() function.
Hope this helps you.
- Sridhar
Hi,
Hope i have understood your question.
Pl look at the attached application.
In my application i have seggregated "Event Date Time" into two time like Enter_Time and Exit_Time using Peek() function.
Hope this helps you.
- Sridhar
Thanks! I've used your approach and my script is working properly now.
Regards,
VK