Attached is a data sample and my current, extremely slow, solution.
I'm searching for a more efficient way to loop through "View_Type" = "Visit" records in my data to identify the same user's next "Viewed_Time" where "Article_CI" matches & the next "Viewed_Time" is within 6 hours. If a record is not returned, add 6 hours to the current record's "Viewed_Time" and continue.
Thanks in advance!
Edit for better explanation:
This is a tough one for me to explain so please bear with me
My data set contains users visiting a webpage and viewing knowledge articles for specific products. A user "Visit" can last 30 minutes and extends as the user hits more articles. If 30 minutes without a hit elapses, a new visit is recorded. Any hits within 30 minutes of a "Visit" are recorded as a "View".
I'm looking to generate the column in green (Incident_WindowEnd) based on user's visit history. This is sorted oldest to newest for my explanation. The first record shows User_73 hit a Product_5 article at 02-06-17 16:56:48 and almost 2 seconds later hit another article for Product_5 which is recorded as a view because it is in the 30 minute visit definition. 48 minutes after the user's visit (02-06-17 16:56:48) they hit another Product_5 article which is recorded as a new visit because it is outside the 30 minute window. I want to set the Incident_WindowEnd value for the user's first visit (02-06-17 16:56:48) to the next visits Viewed_Time (02-06-17 17:44:41) as long as the next visit is within 6 hours, outside 6 hours I want to add 6 hours to the Viewed_Time (highlighted these records in the bluish color). The records highlighted in yellow are visits that should have an Incident_WindowEnd set to the next visit Viewed_Time.
|User_73||Product_5||02-06-17 16:56:48||Visit||02-06-17 17:44:41|
|User_73||Product_5||02-06-17 17:44:41||Visit||02-06-17 18:35:29|
|User_73||Product_5||02-06-17 18:35:29||Visit||02-06-17 19:44:37|
|User_73||Product_5||02-06-17 19:44:37||Visit||02-06-17 20:21:18|
|User_73||Product_5||02-06-17 20:21:18||Visit||02-07-17 02:21:18|
|User_73||Product_5||02-07-17 13:34:22||Visit||02-07-17 15:31:41|
|User_73||Product_5||02-07-17 15:31:41||Visit||02-07-17 16:37:11|
|User_73||Product_5||02-07-17 16:37:11||Visit||02-07-17 17:28:53|
|User_73||Product_5||02-07-17 17:28:53||Visit||02-07-17 23:28:53|
|User_73||Product_5||02-09-17 18:03:48||Visit||02-10-17 00:03:48|
|User_73||Product_5||02-10-17 16:04:17||Visit||02-10-17 20:30:17|
|User_73||Product_5||02-10-17 20:30:17||Visit||02-11-17 02:30:17|
|User_73||Product_5||02-21-17 20:09:12||Visit||02-22-17 02:09:12|