topic Re: Count dimension within unique dimension in QlikView

Count dimension within unique dimension

gdham123 — Wed, 11 Mar 2020 16:32:02 GMT

Hi guys,

I have a table where I am trying to count the number of distinct Child IDs are within a parent dimension. Each Child ID has to be counted once within each Parent ID, but can be counted again if it appears inside another Parent ID. Each parent has to be distinct, and each child has to be distinct within the parent ID.

This is a small sample of the data. The correct answer for Child IDs would be 17, not 22. Parent IDs often appear many times within the larger data-set.

Child_ID	PARENT_ID
QX02809923	a041i00000SsqCIAAZ
QX02205503	a041i00000SsqCIAAZ
QX78342143	a041i00000SsqCIAAZ
QX00541243	a041i00000SsqCIAAZ
QX47972193	a041i00000SsqCIAAZ
QX02809923	a041i00000SsqCIAAZ
QX02205503	a041i00000SsqCIAAZ
QX78342143	a041i00000SsqCIAAZ
QX00541243	a041i00000SsqCIAAZ
QX47972193	a041i00000SsqCIAAZ
QX04588863	a041i00000SsqCmAAJ
QX11389383	a041i00000SsqCmAAJ
QX52572863	a041i00000SsqCmAAJ
QX02629263	a041i00000SsqCmAAJ
QZ64178663	a041i00000UMwojAAD
QZ51922313	a041i00000UMwojAAD
QZ28961483	a041i00000UMwojAAD
QZ00529133	a041i00000UMwojAAD
QZ00137373	a041i00000UMwojAAD
QZ00264053	a041i00000UMwojAAD
QZ43343813	a041i00000UMwojAAD
QZ24072023	a041i00000UMwojAAD

Thanks for your assistance.

Gary

Re: Count dimension within unique dimension

sonkumamon — Wed, 11 Mar 2020 16:55:38 GMT

Try:

Sum(Aggr(Sum(Distinct Child_ID), PARENT_ID))

Re: Count dimension within unique dimension

gdham123 — Wed, 11 Mar 2020 17:17:50 GMT

Thanks Sonkumamon, unfortunately that doesn't seem to work. I've tried

=Count(Aggr(Count(Distinct Child_ID), PARENT_ID))

as well as variations of that, but none of them seem to work on the larger data-set.

Sorry, I should have said that there is also a Calltype that has to be 'GROUP'. So I've also tried:

=Only(Aggr(Count(Distinct {<CallType={'GROUP'}>}Child_ID),PARENT_ID))

=Count(Distinct {<CallType={'GROUP'},PARENT_ID={"=Count(Distinct PARENT_ID)"}>}Child_ID)

and variations of these, but none of them give the correct answer.

Thanks for trying.

Re: Count dimension within unique dimension

sunny_talwar — Wed, 11 Mar 2020 17:21:05 GMT

How about this?

=Sum(Aggr( Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID) , PARENT_ID))

Re: Count dimension within unique dimension

sonkumamon — Wed, 11 Mar 2020 17:29:17 GMT

Hi Sunny,

I would also insert the set analysis in the first expression:

=Sum( {<CallType = {'GROUP'}>} Aggr( Count(DISTINCT {<CallType = {'GROUP'}>} Child_ID) , PARENT_ID))

Just in case.

Re: Count dimension within unique dimension

gdham123 — Wed, 11 Mar 2020 17:38:58 GMT

Thanks Sunny_Talwar this is perfect. It works!!