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?
Solved! Go to Solution.
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.
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.
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.
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