Hi,
Is there a way to aggregate strings a calculation.
Simple use case with following data
|Customer|Product|
|Mark|Software|
|James|Software|
|James|Hardware|
|Mark|Software|
|Mark|Licensing|
I would like to have a table chart where I could have
Mark | Software,Hardware,Licensing
James| Software,Hardware
Is this possible in Quicksight?
Hi Martin,
I don’t think this is currently possible using visuals or calculated fields unless you have a limited predefined list of products. But I don’t this will be good approach, because the code will be difficult to maintain : Let’s say you only have the three products above. You can write the following calculated fields:
Concatenated Products:
concat(
ifelse(
countIf(Customer, Product="Hardware")>0,
"Hardware",
""),
",",
ifelse(
countIf(Customer, Product='Software')>0,
"Software",
""),
",",
ifelse(
countIf(Customer, Product='Licensing')>0,
"Licensing",
"")
)
But you will still need to write more code to clean the commas. Here is a screenshot of the uncleaned version with commas. The Concatenated Products calculated field is used as the Value of a Table visual
A better solution will be to prepare a new table on the backend, and add it as a new dataset to your Analysis.
For now, with minimal effort you can create a visual like the one below. It isn’t very effective if you have a very large list of products.
Another option is to create a master/details design with two visuals with interactions. The first visual will show you the Customers and how many products they have. The second visual will show you the products. When you click on a single customer, you can see the second visual with the corresponding products.
