1 Reply Latest reply: Jan 17, 2018 5:07 PM by Erik Hughes RSS

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

    Erik Hughes

      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)

        • Re: load...lookup/fill next greater date in field x by value from field y.
          Erik Hughes

          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#