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

Really struggling with matching on a field and match a date range and not lose records?

mdorio
7 - Meteor

Hi All,

 

I could use your help.  I'm really struggling with this one.  Here are the details below, and I have attached my flow so far but can't figure out how to get the record with the 2016 stay to be part of the output.

 

I have one file of vacation stays.  Each stay has a plan ID.

I have one file of vacation plans.  Each plan has a date range, a plan name, and a rate.

 

FIle #1:  Stays

NameStay DatePlan ID
Smith7/10/20171
Smith7/10/20181
Smith7/10/20191
Smith7/10/20161
   

 

File #2:  Plans

Plan IDStart DateEnd DatePlan NameRate
14/17/201712/31/2017SummerPlan1400
101/01/201812/31/2018SummerPlan1400
101/01/201912/31/2019SummerPlan1400
101/01/202012/31/2020SummerPlan1600

 

The output I want is this:

 

NameStay DatePlan IDPlan NameRate
Smith7/10/20171Summer1400
Smith7/10/20181Summer1400
Smith7/10/20191Summer1400
Smith7/10/20161Not FoundNot Found
     

 

I do a join on Plan ID, but since I don't have anything else to join on, this creates multiple records for each record in the stay file.  So once I get that output, I filter out where the stay date is within the start date/end date for the plan.  This gets me the actual plan name/rate I want.  But now I have to figure out how to get the record from the stays file that did have a match on plan ID, but I don't want to lose the record if we can't find a valid plan because the stay date is not within start/end...and want to keep it.

 

 
 

Any guidance or advice would be truly appreciated!

 

Mike

example.jpg

2 REPLIES 2
TonyA
Alteryx Alumni (Retired)

How about this? Before you eliminate the out of range rows, sum on the KeepRange flag. Then process all the rows and Union with the rows where the sum of KeepRange was 0.

 

Edit: I tweaked the workflow to line up a little better with your output.

mdorio
7 - Meteor

Thanks so much Tony!  It seems to be working.  I'm going to do some testing on larger datasets to confirm. 

 

Thank you again for your expertise and solution.  Truly appreciated!

 

Labels