topic Aggregation in parent-child hierarchy in QlikView

Aggregation in parent-child hierarchy

Wed, 23 Jun 2010 14:41:31 GMT

Hi, try make aggregation in parent-child hierarchy and have some problems.

I want to understand possibility of QlikView in comparasion with IBM Cognos Planning and Oracle OLAP.

Suppose I have statistics with budget costs in some branchs for several months.

1). I make adjacent nodes table for items (Sheet1)

2).I make fact table (Sheet2)

3). I write this script

T1:

LOAD ItemID,ParentID,ItemName

FROM Cost.xls (biff, embedded labels, table is Sheet1$);

Hierarchy (ItemID, ParentID, ItemName,, ItemName, TreeView) Load

ItemID,ParentID,ItemName

Resident T1;

drop table T1;

Now i have Field TreeView

Problem: There is no aggregation for some items.

For example, QlikView show, that there is no Money value for Item 1.3. Name

But it must be 1.3. Name = (1.3.1. Name + 1.3.2. Name) for these Branch and Montrh

And if I will use hierarchy in branches, months. How can I do that?

Thanks to anyone replying.

Aggregation in parent-child hierarchy

Wed, 23 Jun 2010 15:03:44 GMT

I created Pivot table

Dimension - ItemName, Expression - Money

But there are not hierarchy and aggregation.

I still have a problem.

Aggregation in parent-child hierarchy

Fri, 26 Aug 2011 12:42:44 GMT

Have you found any sollution so far, because I´ve same problem?

Thank´s

Aggregation in parent-child hierarchy

Sat, 03 Dec 2011 09:24:01 GMT

Hi, You should also use the hierarchybelongsto load statement. Give the field names in this table a new name and connect it to your first hierarchy load using your key field.

Aggregation in parent-child hierarchy

mall1m — Fri, 17 Feb 2012 13:50:05 GMT

Hi, did anybody have a solution ?

I'm trying to aggregate indcator along treeview function

any idea ?

Thanks

Re: Aggregation in parent-child hierarchy

Mon, 13 Aug 2012 17:37:48 GMT

did you find any solution? i'm having the same problem

thanks

Re: Aggregation in parent-child hierarchy

Tue, 14 Aug 2012 09:02:03 GMT

Hi,

In order to use hierarchies you need to use both the

hierarchy load statetement (if your input is child, parent), or use your already existing adjacent nodes table.
hierarchy belongs to statement. (with this table you can "attach" something to a node you like, e.g. make a flag for a country, so all its cities also get this attribute associated)

PS: the hierarchy load statement creates new fields for you (costcenter1, costcenter2, costcenter3), you should use the numbered costcenters in your pivot table in order to have correct summarization.

PS2: look very well at the new fields that are created by these statements. You will also have to drop fields to remove synthetic keys. Normally just keep the real key like costcenter_key. In the beginning just use real good fieldnames like "belongs_to_parent", belongs_to_nodename