Employee Movement and Reporting

[KevinK215]

I am trying trying to solve for tracking employee metrics when there is movement within the organization. I have an employee roster file that is updated when someone moves (from one dept to another, changes supervisors, etc.) but it is only updated periodically when a change occurs. I have data points for every employee, every day and I need to ensure the data aggregates properly based on the employee assignment dates in the roster file when we roll-up to the department/region/supervisor level.

I'll try to use a football example to make this a little clearer (see attachment). I have a record of every QB in the NFL and the seasons they played with any given team (roster file). I have a separate table containing their game stats for every game (data file). I want a complete picture of all games played for every QB since 2000. How can I use the To and From dates in my roster file to make sure their game stats get assigned to the correct teams?

This is driving me nuts so any input or suggestions would be appreciated. 

[estherb47]

Hi @KevinK215 ,

I teach a session where we need to look at matches between two ranges. Here, I'd use the Generate Rows tools to generate the dates between the From and To. Then you can join your two tables together and capture the data you need.

Just a moment and I'll upload a workflow example for you.

Cheers!

Esther

[estherb47 · Accepted solution]

Here you go!

Tweaked the data just a touch to replace null values in the "To" field to be the current date, and changed the data type in that column to Date instead of String.


Cheers!

Esther

[CharlieS]

EstherB47 has another solid solution there. Here's an alternative:

[KevinK215]

You guys are awesome. I was really having a hard time trying to figure out how to account for this. Thank you!