load...lookup/fill next greater date in field x by value from field y.

[hugheser27]

I have a table of intervals (Roles) from which I am deriving another table of intervals (Openings) such that a role's end spawns an Opening's beginning.

What I am stuck on is isolating the next date (in time) from a field based on a date value from another field. This is simple when you can peek the next record as you can see I did to populate ToDate in the Roles table, but my data is such that it could be the next record, or the third next record, etc. due to the fact that multiple openings may overlap at any one time.

How can I assign the next (in time) date value from RoleFromDate that is greater than that row's OpeningFromDate?

I have tried Peek (in bold below), joining, and was trying to avoid a loop for each Opening but am open to that now after being stuck on this for some time. Please help!

Roles:

LOAD RoleKey, Indi,RoleTeam, FromDate,

Date(If( Indi=Peek(Indi),

Peek(FromDate) - $(#vEpsilon)

)) as ToDate

Resident Tmp_Roles

Order By Indi, FromDate Desc;

Openings:

Load

RoleKey,

RoleTeam as OpeningTeam,

FromDate as RoleFromDate,

ToDate as OpeningFromDate,

Date(IF( OpeningTeam=Peek(OpeningTeam) ,Peek(RoleFromDate))) as OpeningToDate 

Resident Roles

Order By OpeningTeam, RoleFromDate Desc;

(This works only if there is one opening at a time)

[hugheser27]

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');

temp_todates:

load

Date(Min(RoleFromDate)) as OpeningToDate

resident Openings where OpeningTeam='$(vOpeningTeam)' AND if (IsNum('$(vOpeningFromDate)'),RoleFromDate > '$(vOpeningFromDate)')

Group by OpeningTeam;

next counter#