Display rows based on count of values in field

[saurabhk18]

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 ID	Profit Center	Region
A100	1	Northern US
B200	1	Northern US
A100	2	Southern US
A100	3	APAC
B200	2	Southern US
C300	2	Southern 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 ID	Profit Center	Region
B200	1	Northern US
B200	2	Southern US
C300	2	Southern US

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

Thanks,

Saurabh

[sunny_talwar · Accepted solution]

May be try this in your set analysis

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

[saurabhk18]

Thanks for the quick and correct response!

[saurabhk18]

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]

Oh, of course! Thanks!