Grouping the data

[vlad_komarov]

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

[#2 · Accepted solution]

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

[vlad_komarov]

Thanks! I've used your approach and my script is working properly now.

Regards,

VK