I was able to arrive at the values I want by looping with a group by and where clause but lost the ability to relate the fields in the process (RoleKey is my Key), the group by only allows me to carry fields that are included in the group by.
How can i relate them? Is there a dummy aggr method to retain the key value (text key). or the equivalent of a pandas unstack?
The code below replaced the bold code from the original example:
for counter#=0 to (NoOfRows('Openings')-1)
let vOpeningFromDate = peek('OpeningFromDate','$(counter#)','Openings');
let vOpeningTeam = peek('OpeningTeam',$(counter#),'Openings');
Date(Min(RoleFromDate)) as OpeningToDate
resident Openings where OpeningTeam='$(vOpeningTeam)' AND if (IsNum('$(vOpeningFromDate)'),RoleFromDate > '$(vOpeningFromDate)')
Group by OpeningTeam;