topic Re: Hierarchy tree in QlikView

Hierarchy tree

Tue, 11 Feb 2014 13:02:08 GMT

Hi,

I'd like to make a hierarchy tree out of my database.

Here is an example of my data:

Level GroupID GroupName IdGroupparent

0 22550 Test1

1 22551 Test2 22550

1 22552 Test3 22550

1 22553 Test4 22550

2 22554 Test5 22551

2 22555 Test6 22551

3 22556 Test7 22554

4 22557 Test8 22556

Basically I'd like to build a view like the following suggested in this post: Hierarchy List

However they're using the 'Path' to build the table below. I'd like to automatically be able to organize my Groups from top to bottom.

I've got a maximum of 6 levels.

Result would look like this:

Test1

Test2

Test5

Test7

Test8

Test6

Test3

Test4

Sorry I'm a bit new to Qlikview, if you can provide me some help that would be great.

Thanking you in advance,

Cédric

Re: Hierarchy tree

hic — Tue, 11 Feb 2014 13:20:28 GMT

You should use a Hierarchy load to do this:

Hierarchy (GroupID, IDGroupparent, GroupName, ParentName, GroupName, Path, '/', Depth)

Load

GroupID,

GroupName,

IDGroupparent

From ...

Then you can use the Path field in a list box. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies

HIC

Re: Hierarchy tree

Tue, 11 Feb 2014 14:17:37 GMT

Hi Henric,

Thanks for your quick reply, I don't have any fields such as 'Path' so I can't build the tree.

In your example 'NodePath' is used to build the tree view list box. What can I do to overcome this?

I have done the following:

HIERARCHY([G_ID), [IdGroupe_Parent], [G_Nom]) LOAD

[G_ID],

[IdGroupe_Parent],

[G_Nom]

Resident Database;

Thanks,

Cédric

Re: Hierarchy tree

Tue, 11 Feb 2014 14:26:50 GMT

Cédric,

I wrote a document on that subject:

Hierarchy in QlikView

It may help you. Once you have created your hierarchy, you must get data at upper levels (when choosing Level1, the user will want some data). The doc explains that also (in english).

Fabrice

Bonne lecture

Re: Re: Hierarchy tree

Nicole-Smith — Tue, 11 Feb 2014 14:37:15 GMT

If you have load script like the following, you can use the [Tree] field in a list box:

Hierarchy:

HIERARCHY([G_ID],[IdGroupe_Parent],[G_Nom],,[G_Nom],[Tree],'/',[Depth]) LOAD

[G_ID],

[IdGroupe_Parent],

[G_Nom];

LOAD * INLINE [

Level,G_ID,G_Nom,IdGroupe_Parent

0,22550,Test1,

1,22551,Test2,22550

1,22552,Test3,22550

1,22553,Test4,22550

2,22554,Test5,22551

2,22555,Test6,22551

3,22556,Test7,22554

4,22557,Test8,22556

];

I've also attached a working example.

Re: Hierarchy tree

hic — Tue, 11 Feb 2014 15:17:00 GMT

But you don't need to have the Path field - it is created when you make a Hierarchy Load.

HIC

Re: Hierarchy tree

Tue, 11 Feb 2014 16:09:02 GMT

Hi,

Thank you for your replies, I think I am getting closer!

How to query my database instead of an xls file?

TEST:

HIERARCHY (G_ID, IdGroupe_Parent,G_Nom, [HIERARCHIE], '|', 'HIERARCHY DEPTH')

LOAD G_ID, IdGroupe_Parent, G_Nom

FROM mydatabase.dbo

but it's not working, it keeps looking for a file on my drive instead of SQL

Thanks again!

Re: Hierarchy tree

Tue, 11 Feb 2014 16:12:26 GMT

Then another document may be helpful:

LOAD data into QlikView

The way of loading data from SQL differ a little from a file.

Fabrice

Re: Hierarchy tree

Nicole-Smith — Tue, 11 Feb 2014 16:17:14 GMT

TEST:

HIERARCHY (G_ID, IdGroupe_Parent,G_Nom, [HIERARCHIE], '|', [HIERARCHY DEPTH])

SQL SELECT G_ID, IdGroupe_Parent, G_Nom

FROM mydatabase.dbo;

EDIT: You also need to make sure you're connected to the database through an ODBC or OLE DB connection.

Re: Hierarchy tree

Tue, 11 Feb 2014 16:59:12 GMT

I can now see my newly created Table called "HIERARCHY" and "HIERARCHY DEPHT" however is not doing the hierarchy thing ... like G_Nom with the "|" in between groups. Don't understand why.

I can only see my group without seeing its hierarchy

Hierarchy:

HIERARCHY (G_ID, IdGroupe_Parent,G_Nom, [HIERARCHY], '|', [HIERARCHY DEPTH])

SQL SELECT G_ID, IdGroupe_Parent,G_Nom

FROM ActiveDirectory.dbo."AD_Comptes_ArbActive_V3";

LOAD G_ID, IdGroupe_Parent,G_Nom

Re: Hierarchy tree

Nicole-Smith — Tue, 11 Feb 2014 17:37:57 GMT

Hierarchy:

HIERARCHY([G_ID],[IdGroupe_Parent],[G_Nom],,[G_Nom],[HIERARCHY],'|',[HIERARCHY DEPTH])

SQL SELECT G_ID, IdGroupe_Parent,G_Nom

FROM ActiveDirectory.dbo."AD_Comptes_ArbActive_V3";

Re: Hierarchy tree

Wed, 12 Feb 2014 08:47:23 GMT

Sorry but I can't manage to reproduce the given example... I get my newly created tables HIERARCHY and HIERARCHY DEPTH but the data won't agregate as mentionned.

If I understand the path field is automatically created but in my case unfortunately it doesn't. It's only displaying my G_Nom all alone without the path of child/parent.

Otherwise I can overcome this problem in SQL...

Hierarchy:

HIERARCHY([G_ID],[IdGroupe_Parent],[G_Nom],,[G_Nom],[Tree],"|",[G_Niveau])

SQL SELECT G_ID, IdGroupe_Parent,G_Nom

FROM ActiveDirectory.dbo."AD_Comptes_ArbActive_V3";

Here is part of my tables:

Re: Hierarchy tree

hic — Wed, 12 Feb 2014 08:54:08 GMT

It is difficult for us to find the error without a data sample. However, I can see one small problem: you use double quotes around the path delimiter:

"|"

This is wrong. It must be single quotes:

'|'

See more on QlikView Quoteology

HIC