How to join two many to many tables through a "join/bridge" table in Alteryx?

[sheidari]

I'm stuck solving a many-to-many relationship problem. 

I have two tables, one called 'hoursbyemployee' and another called 'week_ending_calendar'. 

I need to join these two tables but i get duplicates so i'll need to build a bridge table to bring them together.  Is this possible in Alteryx?

[Luke_C]

@sheidari 

Could you share what the end result should look like? 

[sheidari]

Sure, attached is the workflow i built which works fine but as soon as I add another employee with the same week_ending_date as another employee, i get duplicate values at the join. 

The final output is in the attached. 

[sheidari]

Anyone have any ideas on how to solve this? I’m open to new suggestions. 

the problem I’m trying to solve: I need to pro-rate hours that overlap two months in any given week. 

example: if you have 35 estimated hours for week ending 4/3/2021, then 20 of the hours should move to the prior period, 3/27/2021, since 4 of the days falls into March. the remaining 15 hours should stay in the current period. 

[Luke_C]

Hi @sheidari 

Thanks for the details. See attached, I modified your workflow slightly to pro-rate the hours and re-summarized to the new week ending date. The 20 hours in the example was moved to march.  

[Luke_C · Accepted solution]

@sheidari 

Here's another version with the full excel files you attached. (since the dates were already properly formatted in those I removed the datetime tools). This seems to be working across all employees. 

[danilang]

hi @sheidari 

You're getting the duplicated rows after the join because your month calendar has records for multiple days that correspond to each week ending.  If you use a summarize after your select you can reduce this to a unique list of original and new dates

For your prorating, use a formula tool based on the difference between the original and start of month dates for current month and new and start of month for the previous month

Dan

[sheidari]

Thanks @Luke_C it looks to be working now. 

Thanks for getting me to the finish line!