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:

       

         

      TaskIndexWorkStreamTaskSubgroupTaskTacticTask
      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.

       

      Thanks,
      Vince

        • Re: Filling in the Blanks
          Henric Cronström

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

           

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

               Peek(SubgroupTask),SubgroupTask) as SubgroupTask,
          TacticTask
          ;

          Load
          TaskIndex,
          If(IsNull(WorkStreamTask),

               Peek(WorkStreamTask),WorkStreamTask) as WorkStreamTask,

          SubgroupTask,
          TacticTask

          From  ...

           

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

           

          HIC