Multi-row formula, look back a dynamic number of rows?

[rhyatt]

Here is what I'm trying to do...

My data consists of two tables of website activity.  

• Table 1 is search data (a row  for each search result that was displayed). For simplicity sake let's say the fields are:
  • Account number
  • Username
  • Date/time
  • SearchTerm
• Table 2 is cart data (a row for each item that was added to cart).  The fields for this table are:
  • Account number
  • Username
  • Date/time
  • CartItemNumber

So say chronologically a customer searches 3 different terms ('mt dew', 'pepsi', 'coke'), then adds the item number for 'coke' to his cart, then searches 2 more terms ('sprite', 'dr pepper') and adds the item number for 'faygo' to his cart.  

For the 2 cart add rows I want to look back at the prior row with a search term in it (which due to the data structure will NOT be a set number of rows back.  Could be 1 row back, could be 12 rows back) and add that search term in a new column at the end of the cart add row.  

I'm looking at the multi-row formula and it seems like it might work for what I want to do, but the problem I'm facing is that it looks like I have to specify how many rows to look back?  Is there a way to make it look back any number of rows until it finds one with a search term in it, and then return that search term?

Like in the below example, for row 6 i want to simply look back at whatever row last had a search term in it and get that search term (which is row 3).  Same for row 14, I want to get the last search term (row 8).  The fact that there is an unpredictable number of rows in between search terms and cart adds is what's tripping me up here.  Another problem is that the customer can also add an item to cart that wasn't searched through the search box, so that eliminates the possibility of matching terms across rows.

1. accountnumber=100; username=test; date/time= 5/22/17 08:01:00; searchterm=mt dew
2. accountnumber=100; username=test; date/time= 5/22/17 08:01:45; searchterm=pepsi
3. accountnumber=100; username=test; date/time= 5/22/17 08:02:25; searchterm=coke
4. accountnumber=100; username=test; date/time= 5/22/17 08:02:26; pageload
5. accountnumber=100; username=test; date/time= 5/22/17 08:02:27; result generate
6. accountnumber=100; username=test; date/time= 5/22/17 08:02:28; cart add cartitemnumber=207(coke)
7. accountnumber=100; username=test; date/time= 5/22/17 08:04:15; searchterm=sprite
8. accountnumber=100; username=test; date/time= 5/22/17 08:04:50; searchterm=dr pepper
9. accountnumber=100; username=test; date/time= 5/22/17 08:05:26; pageload
10. accountnumber=100; username=test; date/time= 5/22/17 08:05:28; pageload2
11. accountnumber=100; username=test; date/time= 5/22/17 08:05:29;  result generate
12. accountnumber=100; username=test; date/time= 5/22/17 08:05:55;  nav home
13. accountnumber=100; username=test; date/time= 5/22/17 08:06:05;  nav cart
14. accountnumber=100; username=test; date/time= 5/22/17 08:06:48; cart add cartitemnumber=315(faygo)

[NicoleJohnson · Accepted solution]

Not sure if the text input I included at the start of the attached workflow is the same as yours, but should be close... at any rate, I think the multi-row formula should work as shown in the attached. Formula as follows for a new "LastSearchTerm" column:

IF IsNull([searchterm]) THEN [Row-1:LastSearchTerm] ELSE [searchterm] ENDIF

If your data isn't split out in the last few columns the same way as mine, let me know - you can parse out the same information about the searchterm, just takes a few more pieces :) Probably something like this in the same multi-row tool for a new "LastSearchTerm" column:

IF Left([field_____],9)="searchterm" THEN Substring([field_____],9,100) ELSE [Row-1:LastSearchTerm] ENDIF

Let me know if that works!

NJ

[rhyatt]

Thanks Nicole!  Your first solution worked!