Return a matched field value by second field Selection/Value as string?

[ryaneverts]

Hi Qlik Community,

Normally I would use set analysis or a match or Lookup function to get this done, but this is kind of an odd situation.

If a user has a role of standard user, I want to only show data in a straight chart for their corresponding rep name.  If the user has a role of Manager, I want them to be able to see the data for all reps.  Section access has already been ruled out in favor of a calculated dimension for this scenario, as only a small subset of data can be hidden without problems.

For context: a simplified version of our Data Model looks something the following, with a facts and users table:

Facts
Rep
Data to selectively hide

Users
Rep
User
UserRole

What I need to know is: Is there an existing function like match or lookup that, based off of a known User value, can return the corresponding User Role as a string?  Each User will only exist once on the Users table and will have a corresponding role of either User or Manager. The code for the calculated dimension will need to look something like:

IF( $(vReturnedUserRole)='Manager',

Rep,

IF(Rep = $(vReturnedUserRep),

Rep,

Null()

))

Defining the variables in pseudocode as:

vReturnedUserRole = For known User, return matching User Role as string

vReturnedUserRep = For known User, return matching Rep as string

And would be followed by suppressing null values for the calculated dimension.  Let me know if I am going a wrong direction on this or if the approach would be too CPU-intensive.

[ryaneverts · Accepted solution]

Hi all, it look like the ONLY function might fit this need, based on the article: Use of ONLY Function

Found this after posting the question, but I will leave this open until I am able to test that this function works sufficiently for my needs.

EDIT: This does the trick!

Updated variables with testing code follows:

vReturnedUserRole = ONLY({<User = P(User)>}UserRole)

vReturnedUserRep = ONLY({<User = P(User)>}Rep)

Now I only need to replace P(User) with the result of QVUser() (supplied by our SSO) and I'll be in business!

FINAL EDIT:

Switched to OSUser() for the user string due to some misunderstanding about QVUser only supplying Section Access authenticated user info.  Marking as Solved.