Hierarchy problem

[#1]

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

[tresesco]

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.;

[tmumaw]

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

[#4]

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

[#5]

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

[tmumaw]

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

Thom

[#7]

Than yo very much Thome ill check it out

regards.

[tresesco]

You have to use HIERARCHY, something like this:

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

FROM........;

[#9]

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