Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rubenacampos
Contributor III
Contributor III

How do I list multiple users who have the same role on one line in a table?

I have user roles that have multiple users per account.  When I create a table to view the account details the table creates multiple lines per the account for the multiple users.  How would I list each distinct user on one line for each role.  I tried  the following without success:

=AGGR(CONCAT({<[User Role]={'Focal Point'}>}DISTINCT [User Name], ', '), [Contract ID])

I do not want  to use a pivot table b/c it's it doesn't look as clean as a table.  Thanks in advance!

 

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

You dont need an Aggr() here. A simple Concat() with the correct dimensions should suffice:

CONCAT({<[User Role]={'Focal Point'}>} DISTINCT [User Name], ', ')

or add User Role to the dimensions and then

CONCAT(DISTINCT [User Name], ', '),

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
kenphamvn
Creator III
Creator III

Hi

Try this 

 

=Aggr(Concat( distinct [User Name],','),[Contract ID],[User Role]) 
or
=Aggr(Concat( {<[User Role]={'Focal Point'}>} distinct [User Name],','),[Contract ID],[User Role])

Regards

An Pham 

rubenacampos
Contributor III
Contributor III
Author

Pham,

Using the 2nd formula I get the following result (see attached).  Not sure why there is an extra line created per contract ID.

The first formula also gives me multiple lines per contract ID.

Each contract ID has a Focal Point, Reviewing Official, and Approving Official roles but each role can have multiple user names.  Is it possible to have all the Focal Points for a Contract ID on one line (eg, Joe, Nick, John, etc..."

jonathandienst
Partner - Champion III
Partner - Champion III

You dont need an Aggr() here. A simple Concat() with the correct dimensions should suffice:

CONCAT({<[User Role]={'Focal Point'}>} DISTINCT [User Name], ', ')

or add User Role to the dimensions and then

CONCAT(DISTINCT [User Name], ', '),

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein