Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Employee Movement and Reporting

KevinK215
6 - Meteoroid

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. 

 

 

4 REPLIES 4
estherb47
15 - Aurora
15 - Aurora

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
15 - Aurora
15 - Aurora

Here you go!

 

image.pngimage.png

 

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
17 - Castor
17 - Castor

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

 

20190213-RosterDateMatching.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

KevinK215
6 - Meteoroid

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

Labels