Making calculations with values coming from two different tables.

[JaimeB]

Hello there, Alteryx fellows.   

I need your help with performing some calculations. I have two different tables (coming from different inputs) which are the next ones 1) The first one (Hours Planned) contains the working hours which each one of the "resources" (employees) have planned for a certain week (the column headers contain the starting date of that specific week).

2) The second table has the maximum working hours that these specific "resources" can work for a certain week (Workable Hours)

What I want to calculate is the "Overbooking" per week, meaning that I want to compare week by week the hours that a resource has planned versus the hours that this specific resource can really work. An example would be the case of John A. Zoidberg for the week starting on the 13/03/2023: the table "Hours Planned" says that he has a total of 48 hours planned for that week, however he can only work 24 hours for that week, hence the "Overbooking" calculation should be of +24 hours of Overbooking.

Please, how can I do this calculation in Alteryx?

[DataNath · Accepted solution]

Hey @JaimeB, how does something like this look? The main steps are really just up until the Formula tool - everything after that is just around maintaining the order/sort of fields and records:

We basically start by transposing the data so that we have name and date alongside one another in both datasets. Then we can join them together based on those 2 fields and subtract the workable hours from the planned to arrive at our overbooked value. Once we have that, it's simply a case of cross-tabbing back into the original format. Hope this helps but please feel free to ask further questions etc!

[JaimeB]

Hi DataNath!

This is genius! Let me try this approach for my original dataset (which contains way more records and way more features). I will keep you updated!

Thanks, 

Jaime