I'm really keen to figure out an efficient way to solve the following data transformation challenge.
I have a monthly report which provides Employment Status (NEW or EW) for a list of Employees structured like this:
1. My first requirement is to determine which employees have changed status from NEW to EW
2. My second requirement is to count how many months they've been "EW" for.
The table below shows the transformation required (req 1), with the last column containing the count of months, as per req 2 above
I can achieve this, but in a very convoluted way (e.g. searching and replacing NEW and EW with zeros and ones, then pivoting, then replacing back the values). There must be a better way! Any help much appreciated.
Solved! Go to Solution.
Thanks Dilumd for taking the time.
Unfortunately that doesn't get me the time series table with Month as columns and a unique row of employee name where they have converted from NEW to EW.