Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rubenacampos
New Contributor

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
MVP
MVP

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

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
3 Replies
kenphamvn
Contributor III

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

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
New Contributor

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

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..."

MVP
MVP

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

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