topic Hierarchy problem in QlikView

Hierarchy problem

Wed, 23 Mar 2011 17:53:51 GMT

Hello everyone i have a problem and i want to now if someone can help me.

i have a table with this fields: SonID and ParentID these are production parts and i want to know how many sons a parent can have so i used Hierarchy and at first seems to work fine but only worked for some ParentID numbers but not for all of them, heres an example of the data i get from an SQL table

so, what im trying to do is if a SonID shows also as ParetID get that ParentID's SonID and put it as a new level like this

as you can see i found the SonID(9) in the ParentID column and create a new leve with that Parent's SonID, and also a SonID can at a time convert into a Parent and give place to multiple levels like this:

you can have any number of sons and any number of levels.

i apreciate your help in advance and hope that theres a way to do this, also i have the general idea of how to do this with For sentence but i find it hard to realize how to use it in QlikView if anyone knows how can i do this with For sentence ill apreciate as well

thanks

Hierarchy problem

tresB — Wed, 23 Mar 2011 18:33:32 GMT

without understanding your requirement accurately, - if you are only bothered about the number of sons one parent is having, you can use:

Load ParentID, COUNT(SonID) as NumberOfSons From ... GROUP BY ParentID.;

Hierarchy problem

tmumaw — Wed, 23 Mar 2011 18:39:59 GMT

Are you trying to work with the SAP Hierarchy from FI?

Hierarchy problem

Wed, 23 Mar 2011 19:00:24 GMT

No Thom im extracting a table qith both fields from an SQL database

Hierarchy problem

Wed, 23 Mar 2011 19:01:28 GMT

thanks Tresesco but no what i want at the end its a table with al the parents and their respective sons

Hierarchy problem

tmumaw — Wed, 23 Mar 2011 19:12:45 GMT

Alfredo I just sent you an email.....you might be able to use pieces of it.

Thom

Hierarchy problem

Wed, 23 Mar 2011 19:30:57 GMT

Than yo very much Thome ill check it out

regards.

Hierarchy problem

tresB — Wed, 23 Mar 2011 19:42:36 GMT

You have to use HIERARCHY, something like this:



HIERARCHY(SonID, ParentID,Level)
LOAD ParentID,
 SonID,
 SonID as Level

FROM........;

Hierarchy problem

Wed, 23 Mar 2011 19:52:21 GMT

Thanks Tresesco that is actually what im doing but for some reason only works for some Parents not for all of them