How do I reflect team member on Team A before date and Team B after date.

ezmac13
ezmac13 Member
in Beast Mode

I have a team member that has moved teams on a specific date. I need this person to be still reflected in Team A prior to 8/1/23 and also on Team B on and after 8/1/23. All other team members will remain in their current team.

Trying to use beast mode but getting an error. Any help would be appreciated.

case

when Date >= '08/01/2023' and Team Member in 'Name' then Team = 'Team B'

when Date < '08/01/2023' and Team Member in 'Name' then Team = 'Team A'

else Team

end

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    While this solves the particular case keep in mind that this solution is not easy to scale. I'd recommend using a Webform datasource to track Team changes with start and End Date, and then do some ETL to determine the team that person should be in for each entry based on it. Is more work, but that would allow you to have better control on this and can cover more complex scenarios should this be something that can happen in the future.

    The Webform basically would have 3 columns: Team Member Id, Team and JoinDate, you could have a LeaveDate but not mandatory as it'll be determined by the date of the next entry in the list via the same ETL and you wouldn't risk having gaps (of course this means you'll need to populate this the first time for all employees with their current value). Just an idea you might want to consider exploring in the future.

Answers

  • ezmac13
    ezmac13 Member

    I believe I figured it out…

    case when Date >= '08/01/2023' and Team Member in ('Name') then (case when Team in ('Team A') then 'Team B' else Team end) else Team end

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    While this solves the particular case keep in mind that this solution is not easy to scale. I'd recommend using a Webform datasource to track Team changes with start and End Date, and then do some ETL to determine the team that person should be in for each entry based on it. Is more work, but that would allow you to have better control on this and can cover more complex scenarios should this be something that can happen in the future.

    The Webform basically would have 3 columns: Team Member Id, Team and JoinDate, you could have a LeaveDate but not mandatory as it'll be determined by the date of the next entry in the list via the same ETL and you wouldn't risk having gaps (of course this means you'll need to populate this the first time for all employees with their current value). Just an idea you might want to consider exploring in the future.

Categories