Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

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

Highlighted
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
date_timeaccount_numusernameaction_typesearch_termproduct_id_cart
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
date_timeaccount_numusernameproduct_idproduct_id_position
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
date_timeaccount_numusernameaction_typesearch_termproduct_id_cartproduct_id_position
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.

14 - Magnetar
14 - Magnetar

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 :)

 

Lookup.JPG

 

Cheers!

NJ

Highlighted
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.

Highlighted
14 - Magnetar
14 - Magnetar

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

 

:)

 

NJ

Labels