topic Reports with tree field structure in QlikView

Reports with tree field structure

Thu, 17 Jun 2010 10:46:33 GMT

I want to report sales figures in a treeview (product structure).

I'm using the field "Path" with the treeView option, to structure the product view as a tree. That's OK.

What I do not know is how can I report figures on that treeView, as this is a selection field.

Reports with tree field structure

sparur — Thu, 17 Jun 2010 10:48:33 GMT

you can use pivot table with Expanded hierarchy data in dimensions

Reports with tree field structure

Thu, 17 Jun 2010 11:25:22 GMT

Can you tell how to do this , where do I define Expanded hierarchy data in dimensions ?

Thx

Marc

Reports with tree field structure

sparur — Thu, 17 Jun 2010 11:27:56 GMT

if you use

hierarchy statement in the script you create Expanded hierarchy:

Level1, Level2 etc fields.

you can use this fields in pivot table.

Reports with tree field structure

Thu, 17 Jun 2010 11:40:02 GMT

I do not know this instruction. Is it possible to get a script example of Expanded Hierarchy ?

Thank You

Marc

Reports with tree field structure

sparur — Thu, 17 Jun 2010 11:43:40 GMT

Hierarchy
The hierarchy keyword is a prefix to the load and select statements. It is
used to transform an adjacent nodes table to an expanded nodes table.
Hence, the input table must be an adjacent nodes table as described in section
Hierarchies, page 481. The output of the hierarchy prefix is an
expanded nodes table with all the needed additional fields.
Normally, the input table has exactly one record per node and in such a case
the output table will contain the same number of records. However, sometimes
there are nodes with multiple parents, i.e. one node is represented by
several records. If so, the output table may have more records than the input
table.
All nodes without a parent id or with a parent id not found in the nodeID column
will be considered as roots. Also, only nodes with a connection to a root
node - direct or indirect - will be loaded, thus avoiding circular references.
Additional fields containing the name of the parent node, the path of the
node and the depth of the node can be created.
The syntax is:
Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource],
[PathName], [PathDelimiter], [Depth]) (loadstatement | selectstatement)
Where
NodeID is the name of the field that contains the node id.
ParentID is the name of the field that contains the node id of the parent node.
NodeName is the name of the field that contains the name of the node.
300
ParentName is a string used to name the new ParentName field. If omitted,
this field will not be created.
PathSource is the name of the field that contains the name of the node used
to build the node path. Optional parameter. If omitted, NodeName will be
used.
PathName is a string used to name the new Path field, which contains the
path from the root to the node. Optional parameter. If omitted, this field will
not be created.
PathDelimiter is a string used as delimiter in the new Path field. Optional
parameter. If omitted, '/' will be used.
Depth is a string used to name the new Depth field, which contains the depth
of the node in the hierarchy. Optional parameter. If omitted, this field will
not be created.
Example:
Hierarchy(NodeID, ParentID, NodeName) Load
NodeID,
ParentID,
NodeName,
Attribute
From data.xls (biff, embedded labels, table is
[Sheet1$];
The load statement syntax is described on page 309. The select statement
syntax is described on page 329.

....

see in Qlikview reference manual for more details

Reports with tree field structure

Thu, 17 Jun 2010 12:56:35 GMT

OK , but I already use the instrcution HIERARCHY in the script. See below.

//********** Opbouwen van de artikel tree *****************

Artikeltree:

HIERARCHY

// ( NodeID, ParentID, NodeName )

// ( %Key_Product_groep, artparent, artikelgroepoms, , %Key_Product_groep, Path)

// ( %Key_Product_groep, artparent, artikelgroepoms, artparentoms , %Key_Product_groep, Path)

( %Key_Product_groep, artparent, artikelgroepoms, , artikelgroepoms , Path)

LOAD

firnr

& '-' & artgrp as %Key_Product_groep,

//(artgrp of artgrp-fl)

firnr & '-' & left(artgrp,(4*(niv-1))) & repeat( '0000', 8-niv+1) as artparent,

// laatste 4 posities eeraf voor parent te verkrijgen

// toevoegen van x '0000' om aan 8 occurencies te geraken

omschr1 as

artikelgroepoms

FROM

\data\QVD\artgrp.qvd

(

qvd)

;

But questions is how to make a report with the treeView field together with sales data. (and not pivot table fields)