Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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
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;
Nicole thank you very much for this. I have been struggling with this for over half a day. Thank you.
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
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?
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?
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
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;