Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
nicklisterman
Creator II
Creator II

Loop data to find next record where fields match current record

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_UserArticle_CIViewed_TimeView_TypeIncident_WindowEnd
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
7 Replies
adamdavi3s
Master
Master

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  

nicklisterman
Creator II
Creator II
Author

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.

nicklisterman
Creator II
Creator II
Author

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

nicklisterman
Creator II
Creator II
Author

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

adamdavi3s
Master
Master

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

I think the attached is what you want?

Adam

adamdavi3s
Master
Master

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

adamdavi3s
Master
Master

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?