topic Re: Add a field Path in QlikView

Add a field Path

nancy_sbeity — Mon, 20 Jul 2015 06:34:29 GMT

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?

Re: Add a field Path

qlikmsg4u — Mon, 20 Jul 2015 06:43:57 GMT

Hi Nancy,

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

Re: Add a field Path

nancy_sbeity — Mon, 20 Jul 2015 06:49:14 GMT

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.

Re: Add a field Path

Ralf-Narfeldt — Mon, 20 Jul 2015 07:41:55 GMT

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 -

Re: Add a field Path

pokassov — Mon, 20 Jul 2015 07:56:32 GMT

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;