12 Replies Latest reply: Feb 11, 2016 12:39 AM by Avinash R

# Hierarchy and percentage distribution?

Hi,

I would like to know if this is possible to be done with QV. Let's hope I make myself clear.

Asume I have the following records:

 EmployeeID idScheme iTree iPercentDistribution 73506 13 0 80 74251 19 0 20 75226 18 0 100 72416 15 13 50 75226 14 13 50

The iTree column is a reference to its parent located in column idScheme. Each record has a percentage (iPercentDistribution). As you can see the 4th and 5th records (idScheme = 15 & 14) have as parent the 1st row (IdScheme = 13). Records one throug three (IdScheme = 13, 19, 18) have no parents.

The last two records have a distribution percentage of 50% each, but since it's parent only has 80% their percentage must be 40% each. It's there a way to get the following result using Hierarchy?:

 EmployeeID EmployeeID1 EmployeeID2 idScheme iTree iPercentDistribution 73506 73506 13 0 80 75226 73506 75226 14 13 40 72416 73506 72416 15 13 40 75226 75226 18 0 100 74251 74251 19 0 20

Or it should be done using Set Analysis? Honestly, I'm at a loss of ideas here.

Any help would be appreciated.

Regards.

• ###### Re: Hierarchy and percentage distribution?

Hi,

I tried like this..

T1:
Hierarchy(idScheme,iTree,EmployeeID)
EmployeeID, idScheme, iTree, iPercentDistribution
73506, 13, 0, 80
74251, 19, 0, 20
75226, 18, 0, 100
72416, 15, 13, 50
75226, 14, 13, 50
];
Left Join(T1)
LOAD idScheme as iTree,Sum(iPercentDistribution) as Temp_iPercent Resident T1  Group by idScheme;
Left Join(T1)
LOAD iTree,Count(iTree) as Temp_Count Resident T1 where iTree>0 Group by iTree ;

NoConcatenate
Final:
EmployeeID1,
EmployeeID2,
idScheme,
iTree,
if(Len(Trim(Temp_Count))>0 and Len(Trim(Temp_Count))>0,Temp_iPercent/Temp_Count,iPercentDistribution) as iPercentDistribution
Resident T1 Order by idScheme;

DROP Table T1;

• ###### Re: Hierarchy and percentage distribution?

Thanks settu_periasamy, this is clever, I believe this works well if the distribution is always equal between it's children but unfortunate this isn't the case. I my data a parent node could have more than two children and the distribution could be different for each of them.

• ###### Re: Hierarchy and percentage distribution?

Hi,

Can you give more data to test which is more than two children and the expected output? Let's try..

Edit:

I think the below script will work, if there uneven percentage distribution..

T1:
Hierarchy(idScheme,iTree,EmployeeID
EmployeeID, idScheme, iTree, iPercentDistribution
73506, 13, 0, 80
74251, 19, 0, 20
75226, 18, 0, 100
72416, 15, 13, 50
75226, 14, 13, 50
]

Left Join(T1)
LOAD idScheme as iTree,Sum(iPercentDistribution) as Temp_iPercent Resident T1  Group by idScheme

NoConcatenate
Final:

EmployeeID1

EmployeeID2

idScheme

iTree
if(Len(Trim(Temp_iPercent))>0,

iPercentDistribution/100)*Temp_iPercent,

iPercentDistribution) as iPercentDistribution
Resident T1 Order by idScheme

DROP Table T1;

• ###### Re: Hierarchy and percentage distribution?

please provide the same data and the distribution for more than 2 children case ...which will help us to build a logic for the same

• ###### Re: Hierarchy and percentage distribution?

Unbalanced, n-level hierarchies

• ###### Re: Hierarchy and percentage distribution?

Hi,

Try like this

Temp:

EmployeeID, idScheme, iTree, iPercentDistribution

73506, 13, 0, 80

74251, 19, 0, 20

75226, 18, 0, 100

72416, 15, 13, 50

75226, 14, 13, 50

];

Temp1:

idScheme AS iTree,

iPercentDistribution

REsident Temp

WHERE  iTree = 0;

Left Join(Temp1)

iTree,

Count(iTree) AS Count

RESIDENT Temp

WHERE iTree <> 0

GROUP BY iTree;

LEFT JOIN(Temp)

iTree,

iPercentDistribution/Count AS Percent2

RESIDENT Temp1;

DROP TABLE Temp1;

Data:

NoConcatenate

EmployeeID, idScheme, iTree, if(iTree = 0, iPercentDistribution, Percent2) AS iPercentDistribution

RESIDENT Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

jagan.

• ###### Re: Hierarchy and percentage distribution?

Thank you for your answer. As settu's answer I belive this would work great if the distribution is equal among the children nodes which isn't my case sadly . But I appreciate your answer.

• ###### Re: Hierarchy and percentage distribution?

Hi,

Can you provide some more data and you logic for distribution and your expected output.

Regards,

Jagan.

• ###### Re: Hierarchy and percentage distribution?

if the distribution is always equal among the child elements you would follow the settu.periyasamy approach...if into the case than we might need to think for the alternate approach ...let me know your inputs on the same

• ###### Re: Hierarchy and percentage distribution?

Hi Avinash, you're right, settu's approach works good if the distribution is equal, but in my data this isn't the case unfortunately. Do you have any ideas how to make this work for uneven percentage distributions?

Regards

• ###### Re: Hierarchy and percentage distribution?

see the attachment, it might be helpful in generic distribution manner.

• ###### Re: Hierarchy and percentage distribution?

as part of testing, I have added one more row of data and changed the distribution percentage from equal to generic.