Hello Community,
First time poster. I have been using Alteryx for about a month and have learned a lot. However, I am having trouble creating an automated workflow for what has historically been a very manual process. My neophyte skills are too limited to find a solution, and I am hoping for the community's assistance. My background is mostly using Excel.
Below is an example of my data set, my end goal and a description of the manual process for achieving that end goal. Also below is a sample workflow (though very simplified) for what I am trying to do in Alteryx. Please let me know what questions or additional information would be necessary for solving this problem.
Thank you in advance for your insights and time.
The goal is to identify jobs that have accumulated more than 1,800 hours in a given period (i.e., "Hours 1" and "Hours 2") and give that job a special project number. If a job does not meet the hours requirement, I need to find a replacement that matches on multiple criteria: "Job", "Term Date" to "Hire Date" (i.e., approximately 60 days), and total hours >= 1,800. In Excel, I would create a pivot from the data and then manually filter and identify replacements that match all of the criteria, then I would manually add them to my end goal list.
Actual data sets can have a few thousand lines in them, so the manual process is very time consuming. Automating this is a valuable time saver. I have already done a lot of work manipulating data to get it in the format shown in the below example. But this is where I am getting stuck.
Data Set:
| ID | Job | Hire | Term | Hours 1 | Hours 2 |
| 123 | Accountant | 1/1/2017 | 7/1/2018 | 1800 | 1000 |
| 124 | Manager | 4/1/2015 | | 2000 | 2000 |
| 125 | Analyst | 2/15/2017 | 10/1/2018 | 1800 | 1600 |
| 126 | Accountant | 9/15/2018 | | | 300 |
| 127 | Accountant | 4/1/2018 | | | 1000 |
| 128 | Analyst | 5/30/2018 | | | 900 |
| 129 | Accountant | 6/30/2018 | 9/1/2018 | | 750 |
| 130 | Analyst | 10/15/2018 | | | 200 |
End Product:
| Project ID # | ID | Job | Hire | Term | Hours 1 | Hours 2 |
| 1 | 123 | Accountant | 1/1/2017 | 7/1/2018 | 1800 | 1000 |
| 1A | 129 | Accountant | 6/30/2018 | 9/1/2018 | | 750 |
| 1B | 126 | Accountant | 9/15/2018 | (blank) | | 300 |
| 2 | 124 | Manager | 4/1/2015 | (blank) | 2000 | 2000 |
| 3 | 125 | Analyst | 2/15/2017 | 10/1/2018 | 1800 | 1600 |
| 3A | 130 | Analyst | 10/15/2018 | (blank) | | 200 |
Alteryx example is attached.
