Parent and Child Tables

[#1]

Hi

I have a SQL lookup table which has a parent child relationship as below

Parent	Child	Description
NULL	Main Site	Main Site
Main Site	Site 1	Site 1
Main Site	Site 2	Site 2
Site 1	10	Department A
Site 2	12	Department C

So the relationship in a hierarchy would be as below.

Main Site

      Site 1

            Department A

      Site 2

           Department C

What I want to do is create individual dimension for:

Main Site

Sites

Departments

How am i able to do this?  Thank you for your help.

[Nicole-Smith]

The following load script achieves what you're looking for (also see the attached):

T1:

LOAD * INLINE [

Parent,Child,Description

,Main Site,Main Site

Main Site,Site 1,Site 1

Main Site,Site 2,Site 2

Site 1,10,Department A

Site 2,12,Department C

];

Hierarchy(Child, Parent, Description, , Description, TreeView) LOAD

Child,

Parent,

Description

Resident T1;

DROP TABLE T1;

[#3]

Hi 

The problem with the above is that it is explicit in the table contents.  The SQL table contents do change so is there another option?

Thanks

[Nicole-Smith · Accepted solution]

The inline load was just for example data since I can't connect to your database.  Your code should look something like:

T1:

SQL SELECT Child, Parent, Description FROM YourSQLTable;

Hierarchy(Child, Parent, Description, , Description, TreeView) LOAD

Child,

Parent,

Description

Resident T1;

DROP TABLE T1;

[#5]

Nicole thank you very much for this.  I have been struggling with this for over half a day.  Thank you. 

[Nicole-Smith]

If the solution does end up working for you, please mark the correct answer so that other QlikCommunity users can find answers to similar problems

[#7]

One thing I have noticed is that the search criteria object does not show the renamed list box names, but the description fields.  How can this be altered?

[Nicole-Smith]

I'm not sure what you mean.  Can you post a .qvw with what you're seeing and explain what you expect to be seeing instead?

[#9]

Sorry.  So, I have the a list object based on the hieararchy.  When I select a member from this list object, say in the above example Main Site, the current selections object shows what has been selected but the name of the list box is that of the attribuite not the name of the actual list object.  See screen shot:

In addition to this, the example with the hierarchy has worked as I wanted but when I use the List Object and filter by the Select Fields from Table I select from the ********************ExpandedNotes optiion and it shows the fields in the hierarchy but in the example as Description 1, Description 2, Description 3 etc for each of the hiearrachy depths.  Can the name of these be changed in the script?

Thanks

[Nicole-Smith]

Yes, it can be changed.  I'm going to explain by going back to my original example.

So the first two parts below are the original load script that I had written and given to you.  The only thing I added is the last line (the RENAME FIELDS part).  This is how you can rename the numbered fields to what you would like them to be (I'm also attaching a .qvw that depicts this example).

T1:

LOAD * INLINE [

Parent,Child,Description

,Main Site,Main Site

Main Site,Site 1,Site 1

Main Site,Site 2,Site 2

Site 1,10,Department A

Site 2,12,Department C

];

Hierarchy(Child, Parent, Description, , Description, TreeView) LOAD

Child,

Parent,

Description

Resident T1;

DROP TABLE T1;

RENAME FIELDS Description1 to Company, Description2 to Region, Description3 to Division;