topic Re: Need to create hierarchy using only 2 fields in QlikView

Need to create hierarchy using only 2 fields

Wed, 23 Sep 2015 13:39:34 GMT

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

Re: Need to create hierarchy using only 2 fields

pbk — Wed, 23 Sep 2015 14:18:52 GMT

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

Re: Need to create hierarchy using only 2 fields

Wed, 23 Sep 2015 14:33:47 GMT

the

Re: Need to create hierarchy using only 2 fields

Wed, 23 Sep 2015 14:35:44 GMT

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.

Re: Need to create hierarchy using only 2 fields

hic — Wed, 23 Sep 2015 14:50:34 GMT

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

Re: Need to create hierarchy using only 2 fields

pbk — Wed, 23 Sep 2015 15:17:15 GMT

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

Re: Need to create hierarchy using only 2 fields

sudeepkm — Wed, 23 Sep 2015 15:57:00 GMT

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:

Regards,

Sudeep

Re: Need to create hierarchy using only 2 fields

Wed, 23 Sep 2015 17:25:11 GMT

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.

Re: Need to create hierarchy using only 2 fields

sudeepkm — Wed, 23 Sep 2015 19:17:14 GMT

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.

Re: Need to create hierarchy using only 2 fields

Thu, 24 Sep 2015 15:11:21 GMT

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.

Re: Need to create hierarchy using only 2 fields

sudeepkm — Thu, 24 Sep 2015 17:27:28 GMT

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.

Re: Need to create hierarchy using only 2 fields

Thu, 01 Oct 2015 11:25:44 GMT

Yea the 3.5 was from the extended leaves. I was able to address the issue by loading the data from a QVD and then performing a resident load prior to building out the hierarchy. Everything looks good. Thanks for all of the help.