topic Select Max From a String in QlikView

Select Max From a String

evansabres — Wed, 19 Sep 2018 18:35:24 GMT

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.

Re: Select Max From a String

vamsee — Wed, 19 Sep 2018 18:42:09 GMT

Are you looking for

RoleName CountofUserID

Platinum 5

Gold 3

Silver 7

Re: Select Max From a String

evansabres — Wed, 19 Sep 2018 18:43:37 GMT

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

Re: Select Max From a String

vamsee — Wed, 19 Sep 2018 18:50:42 GMT

See if the attached works.

Re: Select Max From a String

evansabres — Wed, 19 Sep 2018 18:56:17 GMT

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

Re: Select Max From a String

vamsee — Wed, 19 Sep 2018 18:57:41 GMT

Sorry Missed your Final Requirement.

Try

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

Re: Select Max From a String

evansabres — Wed, 19 Sep 2018 19:06:12 GMT

This worked wonderfully