topic Parent and Child Tables in QlikView

Parent and Child Tables

Tue, 17 Dec 2013 13:51:05 GMT

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.

Re: Parent and Child Tables

Nicole-Smith — Tue, 17 Dec 2013 15:29:46 GMT

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;

Re: Parent and Child Tables

Tue, 17 Dec 2013 16:15:29 GMT

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

Re: Parent and Child Tables

Nicole-Smith — Tue, 17 Dec 2013 16:17:35 GMT

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;

Re: Parent and Child Tables

Tue, 17 Dec 2013 16:26:55 GMT

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

Re: Parent and Child Tables

Nicole-Smith — Tue, 17 Dec 2013 16:28:06 GMT

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

Re: Parent and Child Tables

Tue, 17 Dec 2013 16:45:28 GMT

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?

Re: Parent and Child Tables

Nicole-Smith — Tue, 17 Dec 2013 17:13:20 GMT

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?

Re: Parent and Child Tables

Wed, 18 Dec 2013 08:46:49 GMT

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

Re: Re: Parent and Child Tables

Nicole-Smith — Wed, 18 Dec 2013 14:01:26 GMT

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;

Re: Re: Parent and Child Tables

Thu, 19 Dec 2013 13:29:04 GMT

Thank you Nicole.