Filling in the Blanks

[#1]

Hey All,

I have data structured as such:

TaskIndex	WorkStreamTask	SubgroupTask	TacticTask
1	Regulatory 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:

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

[hic]

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