topic Re: Need to find hierarchy level in QlikView

Need to find hierarchy level

tamilarasu — Wed, 14 Jan 2015 05:54:36 GMT

Hi Guys,

I need your help on finding hierarchy level. I have data and hierarchy level columns. First I want to take unique data(numbers only and the values which started with text (D_12) not needed i.e 1000 like that). Next, I want to find higher node (by mapping with input sheet) with the unique values (Store as Hierarchy1). Now, there might be chance that I have another higher node for hierarchy1. So again I want to map the hierarchy1 with input sheet. Like wise I have to find upto the top level. I have attached sample excel (Input file which is raw data and I want output like showed in output sheet). Can any one suggest idea on how to do this.

Thank you in advance.

Kind Regards,

Tamil

Re: Need to find hierarchy level

MarcoWedel — Wed, 14 Jan 2015 08:06:07 GMT

Read about Hierarch() LOAD in the help file or the community.

It can create a field that contains the depth/level of a node in a hierarchy.

hope this helps

regards

Marco

Re: Need to find hierarchy level

tamilarasu — Wed, 14 Jan 2015 09:06:07 GMT

Hi Marco,

I have read the Hierarchy syntax but no idea on how to do. Somehow I have managed and created a qvw file, but the result is not what I want. See below screenshot.

Can we achieve this by altering load script as I don't want to do in expressions?

Re: Need to find hierarchy level

tamilarasu — Fri, 16 Jan 2015 02:14:23 GMT

can someone help me.?

Re: Need to find hierarchy level

engishfaque — Fri, 16 Jan 2015 10:19:02 GMT

Dear Tamil,

I have checked your "Sample.XLSX" as well as your "Hierarchy.qvw" file.

But not found any useful hierarchy Parent/Child node/relation name/id or any particular text regarding identification for both.

However, "Hierarchy.qvw" is not working fine. Is this possible to share us actual sample file.

Moving forward, check this thread I hope this will help you a lot.

Hierarchy in QlikView

Kind regards,

Ishfaque Ahmed

Re: Need to find hierarchy level

tamilarasu — Fri, 16 Jan 2015 10:35:26 GMT

Hi Ishfaque,

My source data is two columns and I have renamed the column headers. My node always ends with numbers, so i have no other choice and have to find the top level by extracting the node (numbers) from Node column and respective parent. Again, I have to find the parent by searching in node column and their respective parent. This will go upto 5 hierarchy. I want the result like above screenshot and attached sample excel file again (two sheets input and output). I will check the link and let you if I can figure it out.

Thanks a lot.

Re: Need to find hierarchy level

Sun, 18 Jan 2015 05:11:53 GMT

Hi Tamil,

I could manage to make the output as it was required in the output sheet tab on the attached excel, at this point, what else do you require or what would be the next step? , I believe that from this point forward, We need to do some cleaning and reorganize whatever the HierarchyBelongsTo Function discovered automatically based on your data. Let me know.

Enclosed Qlikview Document and the sample.

Regards,

Mario Estrada

Re: Need to find hierarchy level

tamilarasu — Mon, 19 Jan 2015 04:24:50 GMT

Hi Mario,

Thank you for your time. Sorry, I am not able to see anything in the attached Qlikview document. See the attached file and you might get an understanding on what I wish to do. My data might have five levels and I need to design like same mentioned in excel. The problem is I have two columns and the only way I can find the top level hierarchy is to take the account numbers (Bottom level) and their parent node and so on.

Kind Regards

Tamil

Re: Need to find hierarchy level

tamilarasu — Tue, 20 Jan 2015 04:20:36 GMT

Hi All,

I have found the solution and it's just 1 line of code.

Hierarchy (Node,Parent,Name)

LOAD Node,

Parent,

Node as Name

FROM

[New Samp.XLSX]

(ooxml, embedded labels, table is Input);

Thanks to Marco, Ishfaque and Mario.

Mario: Can you attach the qvw file again. I would like to see your solution

Kind Regards,

Tamil