topic Re: expanded nodes table to hierachy in QlikView

expanded nodes table to hierachy

Tue, 19 Jan 2016 08:27:14 GMT

Hi everybody,

I hope someone could help me with my issue. have an SQL-Database with Elements. This table already include the nodes (add1 to add6) of every element. Now I want to create the Hierarchy but I dont get the order I Need (I think, the depth is missing).

(The Hierarchy should present a fix structure, meaning add1 as parent from add2 is at bottom, add2 as parent from add3 directly above add1 and so on))

One Thing is important. The fields from the SQL-Database must be used. It is not possible to exclude this into an excelfile due to redundancy issues (we unfortunately had that in the past)

Example of Solution (Structured View):

This is the part of the Script that I already have. It is creating the reporting lines but the order is not in there. Everything is mixed up.

Let vHierarchyDefinition = 'Adr1,Adr2,Adr3,Adr4,Adr5,Adr6';
Let vNumberOfLevels = Len(KeepChar(vHierarchyDefinition,',')) + 1 ;

For vAncestorLevel = 1 to vNumberOfLevels
Let vAncestor = Subfield(vHierarchyDefinition,',',vAncestorLevel);

Elm1AddressTree:
Load distinct
/*Upper*/([$(vAncestor)]) as ReportingLine, //Upper converts into CAPITAL LETTERS
RepTreeKey
Resident [oas_elmaddrlist_El1];
Next vAncestorLevel
store Elm1AddressTree into $(DirDataQVD)Elm1AddressTree.qvd;
DROP TABLE Elm1AddressTree; //Element1-Adressen
DROP TABLE oas_elmaddrlist_El1; //Element1-Adressen

Does somebody of you have an idea and could help me with this?

Very much thanks in advance.

Chris

Re: expanded nodes table to hierachy

marcus_sommer — Tue, 19 Jan 2016 12:47:30 GMT

Have a look here: Unbalanced, n-level hierarchies.

- Marcus

Re: expanded nodes table to hierachy

Tue, 19 Jan 2016 15:32:16 GMT

Hi Marcus,

I had a look at your suggessted article and used this to write the code that you see.

But unfortunately I dont get it completely to work.

Maybe you can help.

Thanks

Chris

Re: expanded nodes table to hierachy

marcus_sommer — Tue, 19 Jan 2016 16:03:20 GMT

Your code looked quite diiferent to the approach provided within the link and the sub-links from them. There will be an extra load-statement HIERARCHY used and you tried to get something similar with this one-level loop. I'm not sure but I think that these approach will be rather not work.

I haven't yet used HIERARCHY by myself (I have no use-case for this) but I would try to adapt the logic and would adjust my data appropriate. If this don't worked then would be a small example-app with a few inline-tables helpful.

- Marcus