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: 
saurabhk18
Contributor II
Contributor II

Display rows based on count of values in field

Hi,

In the table below (created using load and join statements), each user ID is associated with multiple profit centers. Each profit center belongs to a single region.

User IDProfit CenterRegion
A1001Northern US
B2001Northern US
A1002Southern US
A1003APAC
B2002Southern US
C3002Southern US

I wish to obtain only those user IDs that are associated with a certain number of profit centers. For example, if I want to see only those user IDs that are associated with 2 or fewer profit centers, I would want to see the following table:

   

User IDProfit CenterRegion
B2001Northern US
B2002Southern US
C3002Southern US

I know it is not too difficult in SQL, but can this be done in QlikView?

Thanks,

Saurabh

1 Solution

Accepted Solutions
sunny_talwar

May be try this in your set analysis

Sum({<[User ID] = {"=Count(DISTINCT [Profit Center]) <= 2"}>} Measure)

View solution in original post

5 Replies
sunny_talwar

May be try this in your set analysis

Sum({<[User ID] = {"=Count(DISTINCT [Profit Center]) <= 2"}>} Measure)

saurabhk18
Contributor II
Contributor II
Author

Thanks for the quick and correct response!

saurabhk18
Contributor II
Contributor II
Author

Sunny, I have a related question: This will help me display the profit centers, but how do I display the Region? The set analysis will work only for numerical values.

sunny_talwar

Add them as dimension

saurabhk18
Contributor II
Contributor II
Author

Oh, of course! Thanks!