topic Loop data to find next record where fields match current record in QlikView

Loop data to find next record where fields match current record

Anonymous — Wed, 22 Feb 2017 06:42:19 GMT

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.

Visit_User	Article_CI	Viewed_Time	View_Type	Incident_WindowEnd
User_73	Product_5	02-06-17 16:56:48	Visit	02-06-17 17:44:41
User_73	Product_5	02-06-17 16:58:46	View
User_73	Product_5	02-06-17 17:44:41	Visit	02-06-17 18:35:29
User_73	Product_5	02-06-17 17:49:21	View
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:02:26	View
User_73	Product_5	02-06-17 19:03:49	View
User_73	Product_5	02-06-17 19:44:37	Visit	02-06-17 20:21:18
User_73	Product_5	02-06-17 19:45:43	View
User_73	Product_5	02-06-17 20:21:18	Visit	02-07-17 02:21:18
User_73	Product_5	02-06-17 20:30:43	View
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 15:33:31	View
User_73	Product_5	02-07-17 15:38:40	View
User_73	Product_5	02-07-17 15:40:24	View
User_73	Product_5	02-07-17 16:01:54	View
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-07-17 17:37:07	View
User_73	Product_5	02-09-17 18:03:48	Visit	02-10-17 00:03:48
User_73	Product_5	02-09-17 18:07:54	View
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-10-17 20:38:13	View
User_73	Product_5	02-21-17 20:09:12	Visit	02-22-17 02:09:12

Re: Loop data to find next record where fields match current record

adamdavi3s — Wed, 22 Feb 2017 07:23:50 GMT

Its kind of hard to tell whats going on with out totally dissecting the code but there has to be a much simpler way of doing this using MIN, DISTINCT and LEFT JOIN

Re: Loop data to find next record where fields match current record

Anonymous — Wed, 22 Feb 2017 15:02:38 GMT

I'll throw together a better explanation I posted late last night after a long day and didn't do well putting this post together.

Re: Loop data to find next record where fields match current record

Anonymous — Wed, 22 Feb 2017 17:00:38 GMT

New write up is in. Hope this makes more sense.

Re: Loop data to find next record where fields match current record

Anonymous — Wed, 22 Feb 2017 18:03:35 GMT

I think I may be able to accomplish this with some smart ORDER BY and PREVIOUS() usage... Have to run some tests.

Re: Loop data to find next record where fields match current record

adamdavi3s — Wed, 22 Feb 2017 18:59:16 GMT

haha yes that is much more straightforward and quick to produce!

I think the attached is what you want?

Adam

Re: Loop data to find next record where fields match current record

adamdavi3s — Wed, 22 Feb 2017 19:00:48 GMT

hang on i've done half a job there and not added the 6hr window....

Re: Loop data to find next record where fields match current record

adamdavi3s — Wed, 22 Feb 2017 19:32:21 GMT

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?