Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Count dimension within unique dimension

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_IDPARENT_ID
QX02809923a041i00000SsqCIAAZ
QX02205503a041i00000SsqCIAAZ
QX78342143a041i00000SsqCIAAZ
QX00541243a041i00000SsqCIAAZ
QX47972193a041i00000SsqCIAAZ
QX02809923a041i00000SsqCIAAZ
QX02205503a041i00000SsqCIAAZ
QX78342143a041i00000SsqCIAAZ
QX00541243a041i00000SsqCIAAZ
QX47972193a041i00000SsqCIAAZ
QX04588863a041i00000SsqCmAAJ
QX11389383a041i00000SsqCmAAJ
QX52572863a041i00000SsqCmAAJ
QX02629263a041i00000SsqCmAAJ
QZ64178663a041i00000UMwojAAD
QZ51922313a041i00000UMwojAAD
QZ28961483a041i00000UMwojAAD
QZ00529133a041i00000UMwojAAD
QZ00137373a041i00000UMwojAAD
QZ00264053a041i00000UMwojAAD
QZ43343813a041i00000UMwojAAD
QZ24072023a041i00000UMwojAAD

 

Thanks for your assistance.

Gary

1 Solution

Accepted Solutions
Highlighted

Re: Count dimension within unique dimension

How about this?

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

View solution in original post

5 Replies
Highlighted
Contributor III
Contributor III

Re: Count dimension within unique dimension

Try:

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

 

Highlighted
Contributor III
Contributor III

Re: Count dimension within unique dimension

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.

 

 

 

Highlighted

Re: Count dimension within unique dimension

How about this?

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Count dimension within unique dimension

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.

Highlighted
Contributor III
Contributor III

Re: Count dimension within unique dimension

Thanks Sunny_Talwar this is perfect. It works!!