Filter on a column where another column is unique

[padraig]

Hi, I am trying to filter on a table like below. When filtering by rank it currently returns all products. However, I am looking to find all products where the country ID has only one rank.

For example

Rank 1 - should return ProductID 1,2,3 - and not 5 as countryID 15 has had rank 2 also.

Rank 3 - should return ProductID 4

ProductID	CountryID	Rank
1	12	1
2	13	1
3	13	1
4	14	3
5	15	1
6	15	2

[chaorenzhu]

can you explain why Rank 2 returns ProductID 6, since the CountryID 15 has two occurrence (both Rank 1 and 2)

[padraig]

Apologies I have edited the question 

[chaorenzhu]

if(aggr(nodistinct count(distinct Rank),CountryID)=1,ProductID)

[padraig]

Chaorenzhu you're a star! Is it possible to extend this to filter on multiple ranks, i.e. filter on rank 1 and 2 and it will return any countries that only have either rank 1 or rank 2, and no other ranks?

[chaorenzhu]

does your prior condition that the CountryID can only occur in one Rank still apply?

[padraig]

The CountryID for only countries which have rank 1 and/or 2, and no other ranks.

[chaorenzhu]

For your CountryID column, use if(aggr(nodistinct max(Rank),CountryID)<=2,CountryID)

[padraig]

Thanks, and if the column had string values e.g 'Rank One' and 'Rank Two', is there a way to check countries only with one of these ranks?

[chaorenzhu · Accepted solution]

In your load script, create another field, say rank_flag, based on Rank value: if(match(Rank,’RankOne’,’Rank Two’),0,1) as rank_flag, then use if(aggr(nodistinct max(rank_flag),CountryID)=0,CountryID)