Hierarchy and percentage distribution?

[alexdataiq]

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.

[settu_periasamy]

Hi,

I tried like this..

T1:
Hierarchy(idScheme,iTree,EmployeeID)
LOAD * INLINE [
    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:
LOAD EmployeeID,
  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;

[jonathandienst]

See HC's blog for more information:

Unbalanced, n-level hierarchies

[jagan]

Hi,

Try like this

Temp:

LOAD * INLINE [ 

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:

LOAD

idScheme AS iTree,

iPercentDistribution

REsident Temp

WHERE  iTree = 0;

Left Join(Temp1)

LOAD

iTree,

Count(iTree) AS Count

RESIDENT Temp

WHERE iTree <> 0

GROUP BY iTree;

LEFT JOIN(Temp)

LOAD

  iTree,

  iPercentDistribution/Count AS Percent2

RESIDENT Temp1;

DROP TABLE Temp1;

Data:

NoConcatenate

LOAD

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

RESIDENT Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

jagan.

[avinashelite]

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

[alexdataiq]

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.

[alexdataiq]

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.

[alexdataiq]

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

[settu_periasamy]

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) 
LOAD * INLINE [ 
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: 
LOAD EmployeeID, 
      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;

[jagan]

Hi,

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

Regards,

Jagan.