String Aggregation based off conditions

Question & Answer
, ,
1

Hi: I am trying to build a calculated field based off certain criteria’s in my table. For example:
In my dataset I have 2 Columns (country and name).

Country Name
France George
Germany Adam
Italy Lily
Italy Paul
USA Fred
USA Jason
France Justin
Germany Michael

In my table, i will like 2 columns, one for country and the second column to consolidate all the names under that criteria (i.e. the country). For example: For USA: the table will show USA in 1 column and in the second column I want the calculated field to show Fred, Jason. Is this possible?

Many thanks in advance!

2

Hey @glam ! Thank you so much for your question.

I don’t think this is currently possible using calculated fields.

My suggestion here would be to preprocess your data in the backend. If you can do a SQL, you can maybe use STRING_AGG/LIST_AGG. And then add it as a new dataset to your Analysis.

Another suggestion, to have a somewhat similar experience, would be using pivot tables:

example
example1526×844 55.2 KB

3

Marking Ana’s @afalcao response as solution.

Regards,
Arun Santhosh
Pr QuickSight SA