Discussion Board for collaboration related to QlikView App Development.
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.
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.
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.
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.
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.
You can do this if you use one record per person, and the second field is the parent. Hence
Load * Inline
This table you can subsequently resolve using a Hierarchy prefix:
Hierarchy (PersonID, Parent, PersonName)
Person as PersonID,
Person as PersonName,
Parent as ParentID
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.
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.
Hierarchy (ResourceID, ManagerID, ResourceName,,ResourceName,[Resource Path],'|',[Resource Depth])
resource as ResourceID,
resource as ResourceName,
manager as ManagerID
From [..\..\hier.xlsx] (ooxml, embedded labels, table is Sheet1);
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.
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.
So now when you select B from the Resource list box it will only show the next level related to B and so on.
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.