Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

alexdataiq
Contributor 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

Re: Hierarchy and percentage distribution?

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

MVP
MVP

Re: Hierarchy and percentage distribution?

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
MVP
MVP

Re: Hierarchy and percentage distribution?

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.





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

alexdataiq
Contributor III

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.

alexdataiq
Contributor III

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.

alexdataiq
Contributor III

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?

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;

MVP
MVP

Re: Hierarchy and percentage distribution?

Hi,

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

Regards,

Jagan.

Community Browser