1 Reply Latest reply on Dec 29, 2018 10:37 AM by Yuriy Fal

    Match transaction to find "name" based on last transaction

    Larry F

      Hi --

       

      I have a set of transactions each with a customer ID, including transactions where a user purchased a type of membership, while other transactions are other types of purchases not related to membership.  For each transaction, I want to be able to identify what type of member made this transaction by using the customer ID, where the identification is based on the most recent membership purchase prior or equal to this transaction date.

       

      An example that comes to mind is with Costco --

       

      Every item (SKU) a member purchases is assigned to that member's ID. I want to be able to assign that item to that member's membership status based on the last time he paid for membership and what that membership type was (gold vs executive; new vs. renew). I've attached a dummy data set.

       

      I'm scratching my head on how to get this started.  I'd presume I will need some sort of calculated field but am stuck.

       

      Thanks in advance!

                                                                                                                                                                                                                                                                                                                                                                  

      NamePriceQuantityCustomerIDDate
      SKU 151222226/1/2018
      SKU 151333332/1/2018
      SKU 151444448/1/2018
      SKU 1515555510/1/2017
      SKU 1556666610/1/2017
      SKU 157777772/1/2018
      SKU 1510888882/1/2018
      SKU 1510999993/1/2018
      SKU 2201111115/1/2018
      SKU 2201222221/1/2018
      SKU 2201555556/1/2018
      SKU 2201666668/1/2017
      SKU 2201888884/1/2018
      SKU 31001999993/1/2018
      SKU 31002111116/1/2018
      SKU 31003222225/30/2017
      SKU 41501555552/1/2018
      SKU 41501666668/1/2017
      SKU 41501777771/1/2018
      SKU 41502888884/1/2018
      SKU 41502999993/1/2018
      SKU 53001555552/1/2018
      SKU 53001666668/1/2017
      New Membership - Gold501111111/1/2017
      New Membership - Gold501222222/1/2017
      New Membership - Gold501333333/1/2017
      New Membership - Gold501444447/1/2017
      New Membership - Executive751555555/1/2017
      New Membership - Executive751666666/1/2017
      Renewed Membership - Gold1001777771/1/2018
      Renewed Membership - Gold1001111111/1/2018
      Renewed Membership - Gold1001888881/31/2018
      Renewed Membership - Executive1501222222/1/2018
      Renewed Membership - Executive1501999993/1/2018