Text to Column

[rubenacampos]

I am trying to create a table by unique account that shows the User Name with their Role and if their are multiple Users with the same role to list the user names in one filed separated by a comma or semicolon.  Unfortunately when I download the data from the source it lists all the users in one column and in the next column it list the role for the account creating multiple rows per account.  How do I create a table with columns for a unique Account, Role 1, Role 2, Role 3, Role 4 and have the users names for each role listed under the respective role?

Thank you!

[OmarBenSalem]

Please a sample, a screenshot of what u have with what u expect.

[Nicole-Smith]

If you want to do it in the load script, you could use script like this: 

Temp:
LOAD * INLINE [
	account, role, user
	1, AM, john
	1, Sales, jane
	1, Sales, harry
	2, AM, joe
	2, AM, sally
	2, Sales, andrew
];

Final:
LOAD account,
	 CONCAT(DISTINCT IF(role = 'AM', user), ', ') AS AM,
	 CONCAT(DISTINCT IF(role = 'Sales', user), ', ') AS Sales
RESIDENT Temp
GROUP BY account;

DROP TABLE Temp;

The above will turn a table that originally looks like this: 

account	role	user
1	AM	john
1	Sales	harry
1	Sales	jane
2	AM	joe
2	AM	sally
2	Sales	andrew

Into a table that looks like this:

account	AM	Sales
1	john	harry, jane
2	joe, sally	andrew

If you would prefer to do it on the chart rather than in the script, you could do so with the following:

Dimension:
account

Expressions: 
CONCAT({<role={'AM'}>}DISTINCT user, ', ')
CONCAT({<role={'Sales'}>}DISTINCT user, ', ')
etc.

[rubenacampos]

Please see the current table output.

[rubenacampos]

Please see the current table output.  Thank you!

[rubenacampos]

What am I doing wrong?  I get an invalid dimension.

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

[Nicole-Smith]

To use it as a dimension, you will need to use aggr().  You cannot use aggregation functions like count in a dimension without the use of aggr().

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