Hello All!
New to the community! I am hoping to contribute and learn as I go.
I am relatively new to Alteryx, but continue to get a stronger grasp with it.
I have a data set with company transaction data and another with a historical record of customer memberships. Please see sample data set below. Would like to add a column to my transaction data that determines if that specific customer had an active agreement at the time of the transaction. The link between the two tables is Customer ID.
Transaction Table
| Customer ID | Business Unit | Location | Date | Transaction Amount $ | Active Agreement at time of Transaction |
| 1111 | Phones | xx | 2010-5-10 | 300 | Yes |
| 1223 | Headphones | xx | 2017-1-1 | 200 | Yes |
| 1111 | Computer | xx | 2013-1-1 | 1000 | No |
| 1223 | Cables | xx | 2018-1-1 | 150 | Yes |
Membership Table
| Customer ID | Membership Type | Membership Start | Membership End |
| 1111 | A | 2009-1-20 | 2010-5-5 |
| 1111 | B | 2010-1-2 | 2012-12-1 |
| 1111 | C | 2014-4-14 | 2020-1-1 |
| 1223 | A | 2017-1-1 | 2017-5-1 |
| 1223 | B | 2014-1-1 | 2025-1-1 |
Goal
-My goal is to use a formula/tool that can go find the specific customer in the membership table then 1) See all memberships and return a "Yes" if the specific transaction falls within(includes the start and end dates of any applicable membership a customer has ever had. If that specific customer is 1) not found or 2) the transaction occurs outside the ranges of memberships it returns a "No"
-There maybe two or three memberships that a customer may have that can overlap. Not concerned with which membership type but more concerned with the date range. Need the formula/tool to be able to see if any of the three memberships' date ranges cover the time period that a transaction took place.
Things to note:
-A customer can have multiple memberships. Some can overlap in the date ranges and some will expire during the time of others. Don't want the formula/tool to accidentally label a transaction as "No" because it sees that a certain membership expired or doesn't cover the date range. It is key that it is able to see all applicable memberships for a customer and return "Yes" if at least one or more memberships cover the date range.
-Memberships don't auto renew all the time. Some customers may be members one year, have two gap years, then return as members.
-Some people sign up as members on the first transaction with the company. So it is common for a customer to have a transaction on 5/1/2015 and also have their membership start on 5/1/2015
-There are millions of transactions so need something that is not manual.
Not sure which tool/tools to use to able to reference the membership table and return an accurate answer to my transaction data. Any suggestions/comments/solutions are definitely welcome. This sort of exercise is new to me in Alteryx, not sure what others have resolved around this.
