topic Re: How to create hierarchy levels in qlikview in QlikView

How to create hierarchy levels in qlikview

Anonymous — Fri, 12 Feb 2016 11:04:18 GMT

Hi All,

Please any help in creating hierarchy levels in qlikview will be appreciated:

Below is code in oracle, where LEVEL field is created, which I want to implement in qlikview:

SELECT employee_id, last_name, manager_id, LEVEL

FROM employees

START WITH manager_id in ('100')

CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL

----------- ------------------------- ---------- ----------

101 Jane 100 1

108 Greenberg 101 2

109 Faviet 108 3

110 Chen 108 3

111 Sciarra 108 3

112 Urman 108 3

113 Popp 108 3

Thanks in advance:)

Regards

Neetha

Re: How to create hierarchy levels in qlikview

MayilVahanan — Fri, 12 Feb 2016 11:09:28 GMT

You can use same Oracle query in Qlikview and create it.

Otherwise, Please refer

Hierarchy and Hierarchy Belongs to

Hierarchies

Re: How to create hierarchy levels in qlikview

Anonymous — Fri, 12 Feb 2016 11:09:32 GMT

This blog post by Henric and it's links are well worth a read :

Hierarchies

Re: How to create hierarchy levels in qlikview

awhitfield — Fri, 12 Feb 2016 11:16:57 GMT

Hi Neetha

There is an example in the QV Help file

Hierarchy

The hierarchy prefix is used to transform a parent-child hierarchy table to a table that is useful in a QlikView data model. It can be put in front of a LOAD or a SELECT statement and will use the result of the loading statement as input for a table transformation.

The prefix creates an expanded nodes table, which normally has the same number of records as the input table, but in addition each level in the hierarchy is stored in a separate field. The path field can be used in a tree structure.

Syntax:

Hierarchy ( NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth] ) (loadstatement | selectstatement)

The input table must be an adjacent nodes table. Adjacent nodes tables are tables where each record corresponds to a node and has a field that contains a reference to the parent node. In such a table the node is stored on one record only but the node can still have any number of children. The table may of course contain additional fields describing attributes for the nodes.

Usually 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 in the input table. If so, the output table may have more records than the input table.

All nodes with a parent id not found in the node id column (including nodes with missing parent id) 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.

Arguments:

Argument	Description
NodeID	The name of the field that contains the node id. This field must exist in the input table.
ParentID	The name of the field that contains the node id of the parent node. This field must exist in the input table.
NodeName	The name of the field that contains the name of the node. This field must exist in the input table.
ParentName	A string used to name the new ParentName field. If omitted, this field will not be created.
ParentSource	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	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	A string used as delimiter in the new Path field. Optional parameter. If omitted, ‘/’ will be used.
Depth	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, ParentName, NodeName, PathName, '\', Depth) LOAD * inline [

NodeID, ParentID, NodeName

1, 4, London

2, 3, Munich

3, 5, Germany

4, 5, UK

5, , Europe

];

Result:

NodeID	NodeName	ParentID	ParentName	NodeName1	NodeName2	NodeName3	PathName	Depth
5	Europe		-	Europe	-	-	Europe	1
3	Germany	5	Europe	Europe	Germany	-	Europe\Germany	2
2	Munich	3	Germany	Europe	Germany	Munich	Europe\Germany\Munich	3
4	UK	5	Europe	Europe	UK	-	Europe\UK	2
1	London	4	UK	Europe	UK	London	Europe\UK\London

Re: How to create hierarchy levels in qlikview

luciancotea — Fri, 12 Feb 2016 11:19:06 GMT

Table1:

Hierarchy(employee_id, manager_id, last_name)

SELECT employee_id, last_name, manager_id, LEVEL

FROM employees

START WITH manager_id in ('100')

CONNECT BY PRIOR employee_id = manager_id;

Re: How to create hierarchy levels in qlikview

Anonymous — Fri, 12 Feb 2016 11:27:28 GMT

Hi Mayil,

Able to create Level using SQL query in qlikview, but when using preceding load on the sql statement, it gives error Level field not found.

Regards

Neetha

Re: How to create hierarchy levels in qlikview

Anonymous — Fri, 12 Feb 2016 11:31:21 GMT

Hi Lucian,

Do we need to add anything before Hierarchy(employee_id, manager_id, last_name) statement please?

Regards

Neetha

Re: How to create hierarchy levels in qlikview

luciancotea — Fri, 12 Feb 2016 11:44:35 GMT

No. Assuming that your SQL statement works, this is what you should have in the QlikView script.

Re: How to create hierarchy levels in qlikview

kaushiknsolanki — Fri, 12 Feb 2016 11:55:17 GMT

Hi Neetha,

You dont need to create any Level in SQL. QlikView will automatically do that.

Find the example attached with this.

Regards,

Kaushik Solanki

Re: How to create hierarchy levels in qlikview

Anonymous — Fri, 12 Feb 2016 12:19:13 GMT

Hi Lucian,

I am getting error OLEDB read failed.

Re: How to create hierarchy levels in qlikview

luciancotea — Fri, 12 Feb 2016 12:42:01 GMT

Then it's a problem with your SQL or connection. It's not a QLikView issue.

Re: How to create hierarchy levels in qlikview

Anonymous — Fri, 12 Feb 2016 12:51:06 GMT

I am getting this error when I use sqlquery and hierarchy prefix together.

Re: How to create hierarchy levels in qlikview

kaushiknsolanki — Fri, 12 Feb 2016 12:53:30 GMT

Dear Neetha,

The Hierarchy function is a QlikView Function and not a sql function.

You can not use it with your sql query.

You will have to fetch below data using select query and then you can use the hierarchy function.

Select EmpID,ManagerId, EmpName From XYZ;

Regards,

Kaushik Solanki

Re: How to create hierarchy levels in qlikview

luciancotea — Fri, 12 Feb 2016 12:57:46 GMT

It should work, according to Help:

The hierarchy prefix is used to transform a hierarchy table to a table that is useful in a QlikView data model.

It can be put in front of a Load or a Select statement and will use the result of the loading statement as input for a table transformation.

Re: How to create hierarchy levels in qlikview

kaushiknsolanki — Fri, 12 Feb 2016 13:01:00 GMT

Thanks Lucian..

Re: How to create hierarchy levels in qlikview

Anonymous — Wed, 17 Feb 2016 16:25:17 GMT

Hi Lucian/Kaushik,

Thanks for both solutions, which are working.

Regards

Neetha

Re: How to create hierarchy levels in qlikview

dineshraj — Mon, 18 Jul 2016 06:07:59 GMT

Hi luciancotea,

Thanks for your solutions.This is Very helpful to me.