Output from multiple fields & scenarios

[robhel]

Hopefully with the amazing expertiese out there, someone can help me work through & solve a really complex & challenging scenario (seeming that case to me anyway!).............

Background:

I exctract staff data each month as a snapshot in time and merge each month into a single file and with this data, track staff movements each month of number new staff or staff that have left, which provides me with the results of =

26,000 rows mergered data & 4,287 of unique Staff IDs with MOM variance using Calendar Date, which provides me with initially what I require.

Then I need to drill further down on this data to ascertain:

1) Laterial move, eg staff member is still within the company just left one business unit & joined a different one

2) Existing staff but converted to a different Staff Type, eg Contractor is now Permanent Staff

Fields for this are:

Staff ID = Unique ID

Staff Type = Permanent Full Time; Contractor; Fixed Term Contract

Business Unit = 100s of Unique IDs

Calendar date = Date of movement/ Change

Step 1) Grouped 4,287 staff list & Count criteria of Staff ID + Staff Type + Business Unit

Step 2) Filtered out Count = 1, returned 200+ records

& this is where I have been able to get to -

Table on left, Lateral Staff Movements & Table on right Lateral Contractor Movements + Contractor converstions to Permanent Full Time staff

Where I need assistance, how can I mash the data to get:

1) staff in & out of business units

2) staff converstions from type, eg contractor to staff

Desired result:

[v-shex-msft]

Hi @robhel ,

I think these fields are extract from different tables based on their relationship, right?
Since I'm not so clear for your table structure and relationship mapping, can you please explain more about these? 
It is hard to test or coding formula without any detail information.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng