Select Max From a String

[evansabres]

I have three fields:

     -  UserID

     -  RoleName

      - RoleRank

UserID,     RoleName,     RoleRank

123456,     Platinum,          8

123456,     Silver,               3

123456,     Bronze,            2

123456,     Blue,                1

987654,     Gold,               4

987654,     Silver,              3

987654,     Blue,               1

Each Unique User ID can have up to 8 different RoleName's.

I am looking to create a table and / or chart that shows the numbers of UserID's in each RoleName category. The tricky part is, if UserID12346 has a RoleName of Platinum, that is the category they should count towards, as that is the highest rank.

[vamsee]

Are you looking for

RoleName CountofUserID

Platinum          5

Gold                3

Silver               7

[evansabres]

Yes, in a way. However, a UserID can be in Platinum, Gold and Silver. I want to select on the occurrence of their RoleName with the highest RoleRank.

Not sure if I should do this in the script

[vamsee]

See if the attached works.

[evansabres]

Script portion functions, how would I then create a chart to count the number of UserID's in each RoleName, keeping in mind I want to keep only the Highest RoleRank for each UserID

[vamsee · Accepted solution]

Sorry Missed your Final Requirement.

Try

COUNT(DISTINCT {<Flag ={1}>} UserID)

[evansabres]

This worked wonderfully