Identify Potential Replacement Employee(s) for Terminated Positions

[Learner2]

Happy New Year! 

I receive an annual payroll report each year that includes the following fields:

• Employee ID
• Job Title
• Hire Date
• Termination Date
• Annual Hours

One of my tasks is to identify potential replacement employees for terminated positions/employees based on the following criteria:

• The employee is active (no termination date)
• The employee was hired within four months before or after the termination date of the terminated employee
• The job title of the replacement employee is similar to that of the terminated employee...e.g., Registered Nurse, Registered Nurse II, RN, etc.
• *This criteria would be preferred in the workflow but may add too much complexity* If the replacement employee was hired prior to the terminated employee (but within the 4-month window), the annual hours must be prorated (reduced) based on the day difference between the termination date and hire date multiplied by 5. Then, the prorated hours of the replacement employee plus the hours of the terminated employee must be equal to or greater than 1,820 (35hrs/week) to be considered a true replacement.

Can anyone think of a workflow that could identify/mark potential replacement employees for terminated positions based on the above criteria?

The multi-row tool comes to mind, but I need to be able to query all possibilities. There could be multiple potential replacements for one terminated employee. I'd like to be able to identify and aggregate all possibilities per terminated employee.

I've attached a small manually created sample report that includes the various scenarios encountered for testing. 

Thanks in advance for any help/advice.

[T_Willins · Accepted solution]

Hi @Learner2,

The only hitch I found is how to categorize the jobs.  I created a Text Input with Job Title and Category.  It could possibly be done with the Fuzzy Match Tool, but it depends on what your data looks like.  After that, I used a batch macro to evaluate each terminated employee individually against the active employees to see which match the given criteria.  Let me know if you have additional questions.

Batch Macro

Workflow

[Learner2]

Hey @T_Willins, 

This solution looks great! Unfortunately, I'm operating on an older version of Alteryx (Version: 2019.3.2.15763) and my company won't be rolling out a firm-wide update until February or March. It appears it only affects the tool used prior to the browse in the workflow. 

I plan to inquire with our IT team whether they'd be willing to update my application prior to the firm-wide update. If they won't, do you know of a comparable tool or workaround to this? 

Thank you for your help. 

[T_Willins]

Hi @Learner2,

What is missing is the macro.  Attached is the workflow and macro in version 2019.2.  Let me know if you have any issues opening it.

[Learner2]

That works!

I like what you did with the +/- 4-month filter criteria - that's a lot more concise than what I had in mind. Also pretty cool how the right employee ID was converted to a string which allowed the concatenate in the summarize tool, but why use the multi-field tool for the conversion rather than just the formula tool since it's only one field being updated? I'm still pretty new to Alteryx, so this solution has been a great learning experience.

I added "+ [Annual Hours]" to one of the macro filters because if the prorated hours of the replacement position + the annual hours of the terminated position are greater than or equal to 1,820, then it qualifies.

Within the macro, could you explain what's going on with the Control Parameter and Action tools? Is it only able to check one terminated position per run or should it be checking all terminated positions? I ask because terminated Employee ID 1 has two potential replacements (Employee ID 2, 3), and terminated Employee ID 4 has a potential replacement (Employee ID 5), but the output of the workflow only shows the replacement found for Employee ID 4. 

I think for the job category problem, I could utilize the fuzzy match with a make group and find and replace tool in some way.   

[Learner2]

Not sure what I did, but I redownloaded the solution and I'm getting the desired output. 

I'll try incorporating the solution into a larger data set once I figure out the job title portion. This will save countless hours for my team and me.

Thank you for your help T_Willins. 

[T_Willins]

Hi @Learner2,

I used the Multi-Field tool instead of a Formula tool as it allows for a changing a Field Type without creating a new field.  It's been requested for the Formula tool as well, so hopefully we'll get that update in the future.  

When I updated the formula in the macro I did get the desired results.  I have attached the updated workflow/macro, so let me know if you are still getting different results.

The macro is a Batch Macro that evaluates each terminated person individually, then aggregates the results for the macro output (like a Union tool).  The reason for evaluating each terminated person individually is the +/- 4 months is specific for each terminated person, which is then compared to the list of active employees to find potential candidates.

If you can get the Fuzzy Match to work it will output an equivalent to the Categories field I created, which you would then Join back to the original data as in the beginning of the workflow.