Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Aggregation in parent-child hierarchy

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)

error loading image

2).I make fact table (Sheet2)

error loading image

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

error loading image

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.

6 Replies
Not applicable

Aggregation in parent-child hierarchy

I created Pivot table

Dimension - ItemName, Expression - Money

But there are not hierarchy and aggregation.

I still have a problem.

Not applicable

Aggregation in parent-child hierarchy

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

Thank´s

Not applicable

Aggregation in parent-child hierarchy

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.

mall1m
New Contributor III

Aggregation in parent-child hierarchy

Hi, did anybody have a solution ?

I'm trying to aggregate indcator along treeview function

any idea ?

Thanks

Not applicable

Re: Aggregation in parent-child hierarchy

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

thanks

Not applicable

Re: Aggregation in parent-child hierarchy

Hi,

In order to use hierarchies you need to use both the

  1. hierarchy load statetement (if your input is child, parent), or use your already existing adjacent nodes table.
  2. 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

Community Browser