Add a field Path

[nancy_sbeity]

Hello, I need your help in this data modeling example:

I have a table for tasks and each task depend on another task.

Example:

TaskID     TaskName     Parent

1                    A               -

2                    B               1

3                    C               2

4                    D               -

5                    E               4

In this example, A and D don't depend on any task. 2 depends on 1 ...

I want to add to this table a new field that represent a path for each dependency, so the table will be:

TaskID     TaskName     Parent     Path

1                    A               -            1

2                    B               1           1

3                    C               2           1

4                    D               -            2

5                    E               4           2

Any suggestions can help me to add this column?

[qlikmsg4u]

Hi Nancy,

Could you elaborate your question? And on what basis A has Path 1 and D has Path 2 ?

[nancy_sbeity]

I only want to draw a path between dependencies:

Since A doesn't depend on any task so this is a new path. B depends on A so it will be in the same path as A.

D doesn't depend on any task also, so this is a new path. I have till now 2 paths. Path one contains 3 tasks and the second task contains 2.

[Ralf-Narfeldt · Accepted solution]

You should do a Hierarchy load, using the Hierarchy prefix. You add Hierarchy (TaskID, Parent, TaskName) before your Load statement. Here, I added it to an inline load of your example.

Table1:

Hierarchy (TaskID, Parent, TaskName) Load * inline [

TaskID,TaskName,Parent

1,A,-

2,B,1

3,C,2

4,D,-

5,E,4

];

The result will be this hierarchy table. As you see, TaskName1 will show the top level for each row.

TaskID    TaskName    Parent  TaskName1    TaskName2    TaskName3

1         A              -      A              -           -

2         B              1      A              B           -

3         C              2      A              B           C

4         D              -      D              -           -

5         E              4      D              E           -

[pokassov]

Hello!

t1:

HierarchyBelongsTo (TaskID, Parent, TaskName,  Parent, ParentName, DepthDiff) LOAD

* Inline [

TaskID,TaskName,Parent

1,A

2,B,1

3,C,2

4,D

5,E,4

];

t2:

LOAD

  TaskID,

  TaskName,

  Parent,

  AutoNumber(Parent) As Path;

LOAD

  TaskID,

  TaskName,

  FirstSortedValue(Parent, -DepthDiff) As Parent

Resident t1

Group By

  TaskID,

  TaskName;

DROP Table t1;