Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Formula/Tool For Searching a Match Between Date Ranges for Active Memberships

Drospeed
5 - Atom

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 IDBusiness UnitLocationDateTransaction Amount $Active Agreement at time of Transaction
1111Phonesxx2010-5-10300Yes
1223Headphonesxx2017-1-1200Yes
1111Computerxx2013-1-11000No
1223Cablesxx2018-1-1150Yes

 

Membership Table

Customer IDMembership TypeMembership StartMembership End
1111A2009-1-202010-5-5
1111B2010-1-22012-12-1
1111C2014-4-142020-1-1
1223A2017-1-12017-5-1
1223B2014-1-12025-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. 

 

5 REPLIES 5
DavidP
17 - Castor
17 - Castor

Here's how I would approach it:

 

active agreements.png

DavidP
17 - Castor
17 - Castor

You probably want to change the < and > in the formula tool (after join) to <= and >=.

Drospeed
5 - Atom

David thank you so much for the help! I was able to get the workflow to work, but running into an issue.

 

It seems that after the join step I am missing some revenue/customers. My total at the end of the workflow is not the same as my starting point. Is this due to the nature of the join tool? 

               - Some of customers will not be in the membership table simply because they never entered into a membership, so they will not be in the table. I was able to check and my remaining revenue is in the left field after join. So I think I can just labeled them all as "No" then append them with my final output. Will let you know if this works. 

 

Also, can you explain to me the thought process behind the summarize tool after the formula tool in regards to the actions for each field. What does the action "first" do for my "Transaction Amount $" and the "Max" due to my Active field. Is the Max the way to try to get if a customer transaction was a membership by taking the max of the potential 1s and 0s? Would like to make sure I understand it thoroughly. 

 

Wouldn't have gotten to this point without the help! 

 

 

 

DavidP
17 - Castor
17 - Castor

You're right, the left output of the join tool shows customers that never had a membership, and yes, you can assign them a "No" and Union them with your final result.

 

You'll notice that I added a recordID to the 1st table before joining it to the 2nd table. Since there could be multiple memberships, every record from the 1st table could be joined to multiple records from the 2nd, as you can see from the fact that there are multiple instances of many recordID's after the join.

 

Each of these can now be evaluated to see if Date falls within an active period and the Active field is set to one if that is the case. With the Summarize tool, we can now group the multiple instances of each of the records of the 1st table together  again by grouping on recordID. 

 

Let's say a Customer had 3 membership periods it will show up as 3 records in the dataset after the join. All the fields from the 1st table will be identical, but the fields from the 2nd table will be unique. If Date only fell within an active membership period in 1 of the 3 cases, the new Active field will have 2 values of zero and 1 value of one for the 3 records.

 

So when we group the 3 records together and we want to keep the fields from the 1st table, it doesn't matter if we take the first, last or max value since they are all the same. But if the max value for the Active field is one, we know that at least 1 of the records had a date within an active period. If the max value of active is zero for a group of a recordID, we know that no Dates fell within an active period. All we have to do now is translate max _Active of one to "YES" and zero to "NO".

 

Does that make sense?

 

Drospeed
5 - Atom

Sorry for the late response, but yes I follow! I was able to apply this to various datasets. 

 

Thanks again! 

Labels