topic Re: Recursive aggregation in QlikView

Recursive aggregation

Wed, 05 Sep 2012 07:25:25 GMT

Hi,

I would like to get the accumulated sum of Task duration. Basically summing up all tasks duration in the task thread. This needs to be done in script.

Any idea's how i can solve this?

Task tree:

level 1	level 2	level 3	Level 4
1
	2
	3
	4
		5
		6
			8
	7

From this:

RootTaskID	TaskID	TriggeredByTask	duration
1	1		2:00
1	2	1	5:00
1	3	1	6:00
1	4	1	7:00
1	5	4	8:36
1	6	4	3:45
1	7	1	6:07
1	8	6	3:28

I should get this:

RootTaskID	TaskID	Accum. duration
1	1	0:00
1	2	2:00
1	3	2:00
1	4	2:00
1	5	9:00
1	6	9:00
1	7	2:00
1	8	12:45

Re: Recursive aggregation

whiteline — Wed, 05 Sep 2012 08:22:54 GMT

As I see, you have a hierarchy of tasks.

Use hierarchy load to create table view of your tree.

There is a plenty of information hierarchy load in the community.

Re: Recursive aggregation

Wed, 05 Sep 2012 09:57:40 GMT

yes. I've already did that. Mine interest, however, is how to sum to recursively sum up all dependent task in the thread.

Re: Recursive aggregation

whiteline — Wed, 05 Sep 2012 14:11:22 GMT

Is Accum. duration for each task calculated correctly in example ?

Re: Recursive aggregation

Wed, 05 Sep 2012 14:55:50 GMT

It should be correct. we're summing up all preceding tasks in the thread.

so for Task 8

Task 1 2:00 +

Task 4 7:00 +

Task 6 3:45 =

total 12:45

Re: Recursive aggregation

whiteline — Wed, 05 Sep 2012 15:10:58 GMT

It's only an idea, but I think you can use it:

RootTaskID	TaskID	TriggeredByTask	duration	step 1	step 2	step 3	result
1	1	-	2:00	-	-	-	0:00
1	2	1	5:00	2:00	-	-	2:00
1	3	1	6:00	2:00	-	-	2:00
1	4	1	7:00	2:00	-	-	2:00
1	5	4	8:36	7:00	2:00	-	9:00
1	6	4	3:45	7:00	2:00	-	9:00
1	7	1	6:07	2:00	-	-	2:00
1	8	6	3:28	3:45	7:00	2:00	12:45

Every step you Left joins TaskID and duration so that TaskID=TriggeredByTask.

So on the fist step you get step1 column.

Then you join values from step1 column with the same rules and get step2 etc.

The result is the sum of those stepN columns.

Re: Recursive aggregation

Thu, 06 Sep 2012 08:21:51 GMT

Nice. But how would I know what StepN is? Task tree depth and structure is dynamic.

Re: Recursive aggregation

whiteline — Thu, 06 Sep 2012 09:00:06 GMT

'While loop' can help you.

As a condition you can chek if there are any values in TriggeredByTask exists to perform a join.

For example in this sample considering the fourth step you should join a table:

TaskID, TriggeredByTask, step3

8, 6, 2:00

But you see that there is no '8's in the TriggeredByTask field. So there is nothing to join.

You can try to perform this comparison in general case.