Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Count where a value in a field has multiple values in another field

I'm trying to figure out a way to count how many ids in a table have more than one instance of another field.  From the table below the Product doesn't matter I'm trying to count how many IDs have more than one Type.  The count for below should be 2.

 

ProductIDType
ProductAID1SCHEDULED
ProductBID1ADHOC
ProductBID2SCHEDULED
ProductCID2SCHEDULED
ProductAID2SCHEDULED
ProductAID3SCHEDULED
ProductBID3ADHOC
ProductDID3SCHEDULED
ProductAID4ADHOC

 

Thanks

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Count where a value in a field has multiple values in another field

Try this at front end:

count(if(aggr(count(distinct Type),ID)>=2,ID))

MC.PNG

Thanks and regards,

Arthur Fong

View solution in original post

3 Replies
Highlighted
Creator III
Creator III

Re: Count where a value in a field has multiple values in another field

At the back end try to use below code:

tmp:

Load distinct 

ID,

count(Type) as total_count

from Table1

group by ID;

 

Load 

ID

resident tmp where total_count>1;

drop table tmp;

 

Highlighted
Digital Support
Digital Support

Re: Count where a value in a field has multiple values in another field

John, did Miskin's post help you get what you needed or are you still working on things?  If it did work, be sure to come back to the thread and use the Accept as Solution button on that post to give credit for the help and to let others know that was a good solution.  If you still need help, leave an update.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Partner
Partner

Re: Count where a value in a field has multiple values in another field

Try this at front end:

count(if(aggr(count(distinct Type),ID)>=2,ID))

MC.PNG

Thanks and regards,

Arthur Fong

View solution in original post