We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Join to only the most recent by date/time matched data row...?

8 - Asteroid

I've been playing around with this for a while and am struggling to find a good way to do this in Alteryx.  My data set is chronologically ordered events on a website.  


For instance, the below table shows a single customer and his/her actions in a 5 minute time span.  The customer first searched for advil, and then added advil product_id 101 to cart.  They then clicked product info for a diff recommended product, then searched for tylenol, searched for advil again, and finally added another  advil product (id 101) to their cart


Website Interaction Data
6/1/17 10:01 AM2655test1advil 
6/1/17 10:02 AM2655test3 101
6/1/17 10:03 AM2655test2  
6/1/17 10:04 AM2655test1tylenol 
6/1/17 10:05 AM2655test1advil 
6/1/17 10:06 AM2655test3 101


For each search_term the customer enters into the search box, the website will display several product_id's that are relevant.  Those product_id's returned for each search term live in a separate table.


Search Lookup Table
6/1/17 10:01 AM2655test1001
6/1/17 10:01 AM2655test1012
6/1/17 10:01 AM2655test1023
6/1/17 10:03 AM2655test2001
6/1/17 10:03 AM2655test2012
6/1/17 10:03 AM2656test2023
6/1/17 10:04 AM2655test1011
6/1/17 10:04 AM2655test1002
6/1/17 10:04 AM2655test1023


What I want to do is this:  For each time a product_id is added to cart, I want to say what position/order it was in the displayed results list. So if 3 results were displayed for a search and the customer added the 3rd one to cart, I want to add a column to the first table that has a 3 in the cart add row.


The problem I'm having is that a customer can search an unlimited number of terms before adding to cart.  So when I join on acct, username and date (not the time portion since the cart add will be post-search and have a later time stamp.) it is possible that the cart add product_id will match to multiple product_id's in the search lookup table, and will then return multiple different product_id_position values. Unfortunately the position/order of the displayed results is not set, it can vary by search. 


So what I'm getting is this:

Final Table
6/1/17 10:01 AM2655test1advil  
6/1/17 10:02 AM2655test3 1012
6/1/17 10:02 AM2655test3 1011
6/1/17 10:03 AM2655test2   
6/1/17 10:04 AM2655test1tylenol  
6/1/17 10:05 AM2655test1advil  
6/1/17 10:06 AM2655test3 1012
6/1/17 10:06 AM2655test3 1011


It's giving me the position 2 for the 10:01 am advil search, and the position 1 for the 10:04 am advil search on each cart add.  What I want is to just get the position for the most recent search (position 2 on the first cart add, and position 1 on the 2nd cart add in this example).  I'm not finding a way to look back and join only to the most recent match.  


I've tried messing around with the multi-row formula tool, but I'm not having any luck.


I'm hoping there is an alteryx tool I'm missing that might help with this!  Any help is greatly appreciated.

ACE Emeritus
ACE Emeritus

How about something like the attached?


Made a couple assumptions:

1. For your website data, I assigned "action numbers" based on starting over at 1 any time a search term is entered. So in your table below, records 1-3 would be action #1, row 4 would be action #2, and row 5 would be action #3.

2. Made a similar assumption for the lookup table, and assigned a new "action number" anytime product_id_position started over at 1.


This allowed me to match "actions" (i.e. the lookup table associated with each search term). Does that make sense? Let me know if that helps at all :)






8 - Asteroid

This sounds very promising, I think this might work!


Can you post the code from one of your multi-row formula boxes? Just want to make sure i'm interpreting it correctly.

ACE Emeritus
ACE Emeritus

Top one (web data) = if !isnull([search_term]) then [Row-1:ActionNumber]+1 else [Row-1:ActionNumber] endif


Bottom one (lookup) = if [product_id_position]=1 then [Row-1:ActionNumber]+1 else [Row-1:ActionNumber] endif