Merging Employee Historical Data with Client Assignments based on Coverage Timeframe.

[edmund_belleza]

Hi, relatively new to Alteryx here.  I'm in need of some help and hoping someone in this community may be able to help me.

I have two reports that I want to combine data for. The first tab is a historical listing of Employees by month for the past few years. The second tab is a listing of Employee's and their assigned clients (ID), as well as their Start/End dates for when the Employee were assigned the client.

I'm trying to figure out a work flow that will bring the ClientID's in for each employee on the Historical Report, but only list that specific client (or clients) for the specific months based on the start/end date in the Client Assignments. Client assignments can change over time. It's possible for an Employee to have more than one client at one time. (If so, can both clients be listed in that one row or would it be duplicate rows? not sure which is possible) It's also possible for an Employee to not have any clients during a specific time frame.

For example, if John Smith was assigned Client ABC from June 2019-August2020, Client ABC will only appear in the John Smith's rows for dates between June 2019-August2020 in the historical report.  If there is currently no end date listed, then it's a current client assignment.

it sounds like a conditional find/replace of some sort, but I sense it is more complicated then that. What would be the best way to accomplish a merge like this?

Any help would be greatly appreciated. Thank you.

[AngelosPachis · Accepted solution]

Hi @edmund_belleza ,

I have created a workflow that might do what you are looking for. Essentially I used a generate rows tool to create all the dates from the start date to the end date for each client assignment. For clients with no end date, I replaced that "-" with today's date, and generated all dates until then.

The I joined with the employee file on employee ID and the date field, so now you know which employees worked with your clients over the given period.

There are some records that fall out of the L output anchor of the Join tool, and these are employees that have not matched with a client over that certain timespan. 

I have then used a flag for the different clients to distinguish if it's a Current client assignment or not.

Let me know if that works for you and feel free to ask any questions. Hope that helps.

Regards,

Angelos

[edmund_belleza]

Angelo,

Thank you so much for taking time to help me with my workflow. At first glance, I think your solution will work. I'll have to adapt it to our data set and validate the export to make sure it's what my team is needed. It's definitely closer than where I was yesterday. I'll follow-up with any questions after we adapt/test/review.

You help with his is GREATLY appreciated!

[edmund_belleza]

I was able to adapt your workflow with our data and it seems to be working as expected. Thanks again for your help!