Hierarchy tree

[#1]

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

[hic]

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

[#3]

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

[#4]

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

[Nicole-Smith]

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.

[hic]

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

HIC

[#7]

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!

[#8]

Then another document may be helpful:

LOAD data into QlikView

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

Fabrice

[Nicole-Smith]

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.

[#10]

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