7 Replies Latest reply: Feb 22, 2017 2:32 PM by Adam Davies RSS

    Loop data to find next record where fields match current record

    Nick Listerman

      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_73Product_502-06-17 16:56:48Visit02-06-17 17:44:41
      User_73Product_502-06-17 16:58:46View
      User_73Product_502-06-17 17:44:41Visit02-06-17 18:35:29
      User_73Product_502-06-17 17:49:21View
      User_73Product_502-06-17 18:35:29Visit02-06-17 19:44:37
      User_73Product_502-06-17 19:02:26View
      User_73Product_502-06-17 19:03:49View
      User_73Product_502-06-17 19:44:37Visit02-06-17 20:21:18
      User_73Product_502-06-17 19:45:43View
      User_73Product_502-06-17 20:21:18Visit02-07-17 02:21:18
      User_73Product_502-06-17 20:30:43View
      User_73Product_502-07-17 13:34:22Visit02-07-17 15:31:41
      User_73Product_502-07-17 15:31:41Visit02-07-17 16:37:11
      User_73Product_502-07-17 15:33:31View
      User_73Product_502-07-17 15:38:40View
      User_73Product_502-07-17 15:40:24View
      User_73Product_502-07-17 16:01:54View
      User_73Product_502-07-17 16:37:11Visit02-07-17 17:28:53
      User_73Product_502-07-17 17:28:53Visit02-07-17 23:28:53
      User_73Product_502-07-17 17:37:07View
      User_73Product_502-09-17 18:03:48Visit02-10-17 00:03:48
      User_73Product_502-09-17 18:07:54View
      User_73Product_502-10-17 16:04:17Visit02-10-17 20:30:17
      User_73Product_502-10-17 20:30:17Visit02-11-17 02:30:17
      User_73Product_502-10-17 20:38:13View
      User_73Product_502-21-17 20:09:12Visit02-22-17 02:09:12