Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexdataiq
Partner - Creator III
Partner - Creator III

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:

  

EmployeeIDidSchemeiTreeiPercentDistribution
7350613080
7425119020
75226180100
72416151350
75226141350

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
7350673506 13080
752267350675226141340
724167350672416151340
7522675226 180100
7425174251 19020

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

Any help would be appreciated.

Regards.

12 Replies
settu_periasamy
Master III
Master III

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;

Capture.JPG

jonathandienst
Partner - Champion III
Partner - Champion III

See HC's blog for more information:

Unbalanced, n-level hierarchies

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jagan
Luminary Alumni
Luminary Alumni

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
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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
Master III
Master III

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
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.