I came upon a problem with source data that I need to solve.
I have a table with All Employees column that has several names that I would like to extract and fill other columns with. Please see example below where I have the All Employees from raw data and I have to fill all other ones on the right.
Task|All Employees |Lead Employee1|Lead Employee2|Lead Employee|Reg Employee1|Reg Employee2|Reg Employee
1 |Mark Emily Robert|Mark |Emily |Multiple |Robert |NULL |Robert
2 |Mark Robert |Mark |NULL |Mark |Robert |NULL |Robert
3 |Robert |NULL |NULL |NULL |Robert |NULL |Robert
There's around 50 employees and a small rotation (people come and go). The easiest solution would be to use several nested IIFs for every group (more or less 20 employees per group). That would mean changing the IIF every time there is a change in the team. I was thinking of streamlining it a bit and use additional table where I could keep track of current and previous employees below.
Team members table
Employee|Position
Mark |Lead Employee
Emily |Lead Employee
Robert |Reg Employee
There should be one employee per group assigned to a task so I have to keep track of all situations where there is a multiple of them (handing over a task to a colleague for vacation for example). I don't have a problem with getting data for a group (simple WHERE clause) but I don't know if there is a way to use some LIKE expression that would check if there is any occurence of (for example) a Lead Employee and fill a table with it. I know that filling the second column would be easier because I would use similar query and just exclude already found employee (replace it with an empty string).
Can you tell me of this is doable (if yes please give me some hint or direction) or should I stick with nested IIFs?
