Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to create hierarchy using only 2 fields

I need to create a hierarchy utilizing only 2 fields.  The parent field is 'Resource Manager' and the child is 'Resource'.  The relationship already exists between the two fields, the problem is it only shows the direct Resources associated to the Resource Manager.

Example:

Resource Manager:  Simon

Resources:  Tom and Scott

Resource Manager:  Tom

Resources:  Betty and Jean

Resource Manager: Scott

Resources:  Chris, Tyler

With the above example if I select Simon, I'm only able to see his direct resources Tom and Scott.  I want to be able to see Tom and Scott as well as their direct resources. So I would like to see Tom, Scott, Betty, Jean, Chris and Tyler.  How do I setup a hierarchy like this when I only have 2 fields, the Resource Manager and Resource.

Thanks

1 Solution

Accepted Solutions
sudeepkm
Specialist III
Specialist III

3.5 billion records seems huge. Is that the no of records present in source table or the results you are getting from the hierarchy load. I think it would be better to consider below given approach while building it.

1. Check if you can find a flag field in the data source table that would indicate a record as active so that you can limit your data load to active resources only.

2. Load resource data in a qvw by implementing an incremental load so that it won't load the history repeatedly and then store the data in a qvd.

3. Create a separate QVW just for hier QVD generation a single job dedicated for hierarchy data load. (I think it would be better to call the hier function on the data from a table within that qvw loaded from a QVD) I know there are 19 levels or more but in the past I have used the hierarchy function to generate LOB structure that spanned until 15 levels and the data load had passed our quality review.

4. The UI will load data from Hier.qvd

The active resource count should be much lessor than all resources if that can be found then the load will be easy.

View solution in original post

11 Replies
pbk
Employee
Employee

In your load script, use the Hierarchy function to recurse through the data that represents Mangers and Employees. Use the resulting structure, specifically the 'PathName' as a delimited path, in a List object.  Set the 'Show as TreeView' property on the General tab of the Listbox.  You should get what you are looking for.

...PB

Not applicable
Author

the

Not applicable
Author

Thanks for the response.  The problem is I need to develop multiple levels of resource managers.  From the example I provided you can see that I need to be able to select Simon and see all of his directs and their directs.  So basically I need to find a way to build multiple levels of the manager hierarchy with only 2 fields. 

hic
Former Employee
Former Employee

You can do this if you use one record per person, and the second field is the parent. Hence

Load * Inline

[Person, Parent

Simon,

Tom, Simon

Scott, Simon

Betty, Tom

Jean, Tom

etc... ];

This table you can subsequently resolve using a Hierarchy prefix:

Hierarchy (PersonID, Parent, PersonName)

Load

     Person as PersonID,

     Person as PersonName,

     Parent as ParentID

     From ...

HIC

pbk
Employee
Employee

HIC beat me to it....

The Hierarchy function requires 3 input fields. But of course you can use the same field twice, as in HIC's example.

...PB

sudeepkm
Specialist III
Specialist III

Hi Jeremy,

How are you. long time...

Hope all is well.

I'm just adding little bit more to HIC's response. In case you have a requirement to display the hierarchy in a list box then you can use the below approach.

Script:

Hierarchy (ResourceID, ManagerID, ResourceName,,ResourceName,[Resource Path],'|',[Resource Depth])

Load

    resource as ResourceID,

    resource as ResourceName,

    manager as ManagerID

From [..\..\hier.xlsx] (ooxml, embedded labels, table is Sheet1);

UI:

T182043.PNG

Regards,

Sudeep

Not applicable
Author

Gentlemen I'm getting close here.  Thanks for all of the responses.

Sudeep, what's going on man?  We will need to catch up offline.  In regards to your post I tested the code you provided and it covers half of the requirement.  The problem is, I want the ability to select Resource B from the hierarchy and see resource B1, B2 and then B11, B12, B13, B21 and B22 since they are associated to B1 and B2.  With the approach you provided I would need to highlight all of them individually in order to accomplish this.

Is there a way to just select Resource B from a list box and all of the children are automatically selected.    

sudeepkm
Specialist III
Specialist III

yeah maybe you can use a drill group named as "Resource" having all the level of resource names and then use that drill group in your list box.

T182043_2.PNG

So now when you select B from the Resource list box it will only show the next level related to B and so on.

T182043_3.PNG

Not applicable
Author

Thanks again Sudeep, for some reason I did not even think of a drill group.

In theory the solution you provided should work, the problem is as you may know I'm working with a very large data set (all resources).  There can be as many as 20 levels of resources so when I run the script it runs for hours.  I left it run yesterday afternoon and it ran for 10 hours returning 3.5 billion rows of data.  All of my memory was sucked up so it shut down at that point.  I'm currently running it off the server and I'm at 3 hours now and it's still running, which does not meet our standards.

Are there any other ways to build out this hierarchy.