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|
Ok this should do it, except I can't work out why it is adding too many hours to the timestamp in the script as the formula is fine.... and its late and its making me cross, but it should do it for you after a little tinker?