Top 5 records

[paulwalker]

Hello Experts,

I have data like below.

few names has multiple status values (Like True and False), and last column id ID count.

Here I have to get top 5 names which count ID is high.

(Ex: Sunil has two records, I have to fetch 1st record because ID count is higher) and If it is null Status we have to exclude.

Name	Status1	Status2	Status3	Count (ID)
Blank	-	-	-	62322
Sunil	True	True	False	29834
Paul	True	True	False	17487
Sunil	True	True	True	16816
ABC	-	-	-	13560
Ninja	True	True	True	12504
Natalia	False	True	False	8847
Gosh	True	True	False	8710
CarMark	True	True	False	8272
Book	-	-	-	7846
CarMark	-	-	-	7713
Pen	-	-	-	6912
Naina	-	-	-	6882
CarMark	True	True	True	6059

output should be..

Name	Status1	Status2	Status3	Count
Sunil	True	True	False	29834
Paul	True	True	False	17487
Ninja	True	True	True	12504
Natalia	False	True	False	8847
Gosh	True	True	False	8710

@tresesco, @MayilVahanan,  @Kushal_Chawda, @marcus_sommer, @stevedark 

and experts can you please take a look!

Thanks in Advance!

[vincent_ardiet_]

Removing the second "Sunil" seems to be the tricky part.
Else, first, if you want to get rid of the null status, you can change your expression with:
Count(ID)/RangeMax(0,Only(Status1),Only(Status2),Only(Status3))
This will create division by 0 for those lines and they will disappear.

Something like this may work but you will not be able to display the status columns:
Aggr(Max(Aggr(Count(ID)/RangeMax(0,Only(Status1),Only(Status2),Only(Status3)),Name,Status1,Status2,Status3)),Name)

[paulwalker]

Thanks for response Vincent!

if they have numeric we can use RangeMax, but they have flag like TRUE and FALSE and updated main post.

[paulwalker]

Experts, could you please help me on this 

[Tanish]

Hi @paulwalker ,

I have recreated your issue and check out the solution what i found.

1. Add all the fields required in the Table.

2. Add Measures as 

                      Aggr(Max([Count (ID)]), Name)

3. Deselect the Include Null value check box for all the fields.

4. Under Add-Onn Tab -> Data Handling - Deselect the Include Zero value check box.

Here is the output what you looking for...

Hope this will help.....

Thanks,

Tanish