Upgrading Server to version 22.1? We’ve enabled the faster AMP engine by default. Read about how these system settings changes might affect you.

We’re aware of an intermittent issue with our My Alteryx login and are actively working to have the issue corrected. If you run into an error when logging in, please try clearing all cookies or accessing the community on a different browser. Thank you for your patience!

Alteryx Designer Discussions

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

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

sheidari
8 - Asteroid

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?

7 REPLIES 7
Luke_C
16 - Nebula

@sheidari 

 

Could you share what the end result should look like? 

sheidari
8 - Asteroid

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
8 - Asteroid

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
16 - Nebula

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_0-1616680092308.png

 

Luke_C
16 - Nebula

@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
18 - Pollux
18 - Pollux

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

danilang_0-1616681024396.png

 

Dan

sheidari
8 - Asteroid

Thanks @Luke_C it looks to be working now. 

 

Thanks for getting me to the finish line!

Labels