1 Reply Latest reply: Oct 7, 2015 5:48 AM by Henric Cronström RSS

    Filling in the Blanks

    Vince Agresto

      Hey All,


      I have data structured as such:



      1Regulatory Submission Miestones--
      2-Pre- Meeting (Rapporteur's mtg)-
      3-SPC Final for submission-
      4--JDC Endorsement
      5--JSC Endorsement
      6--Final Label in DOMASYS
      7-MAA filing (LDL-c)-
      8-MAA filling (LDL-c)-
      9-MAA Review (10months)-
      10-1st Action Date (10months)-
      11-Approvable Letter Received-
      12-Complete Response-
      13-MAA Review of Response-
      14-MAA Approval-
      15-US Label Final for Submission-
      16--JDC Endorsement
      17--JSC Endorsement
      18--Final Label in DOMASYS
      19-BLA Filing-
      20--BLA filing (LDL-c)
      21--BLA filling (LDL-c)
      22--BLA Approval



      I want to fill in the hierarchy - i.e.

      - the TacticTask should always have a Subgroup and WorkStream

      - the Subgroup should always have the related WorkStream


      In the data there will be many WorkStreams - but the order of relationship is always determined by the index - i.e. 23 could be a new WorkStream with a similar hierarchy below.


      The way I have been able to start to fill in in the following syntax:

      fill info.jpg


      This works one row at a time - and I am able to keep repeating it with new Field iterations below in the script- but the amount of Subgroups under a WorkStream is variable.


      Is there a way to fill them all in at once, or run a loop so I only have to build the table one time.



        • Re: Filling in the Blanks
          Henric Cronström

          You can do it in one pass using a preceding Load:


          If(IsNull(SubgroupTask) and WorkStreamTask=Peek(WorkStreamTask),

               Peek(SubgroupTask),SubgroupTask) as SubgroupTask,


               Peek(WorkStreamTask),WorkStreamTask) as WorkStreamTask,


          From  ...


          You need one preceding Load per level you want to fill.