Assistance Needed for Combining Fields in QuickSight Analysis Without SQL

Hi All,

I am currently working on an analysis in Amazon QuickSight and need assistance with displaying data from two related tables.

Here is the setup of the two tables I am using:

  • Table 1 (car_main): Contains the fields car_id and car_name.
  • Table 2 (car_owner): Contains the fields car_id and owner_name.

In my analysis, I am primarily working with the car_main table, but I would like to display a field in the data table that shows the owners (from the car_owner table) for each car as a comma-separated list.

We are aware that this could be achieved using SQL, specifically by using a STRING_AGG or a similar function. However, we are trying to separate the datasets from any SQL manipulation and would prefer a solution that can be implemented directly via the QuickSight console — either in the analysis or dataset preparation phase.

Example Scenario:
If car_id = 1 corresponds to car_name = "Toyota", and the car_owner table contains two records for this car (owner_name = "John" and owner_name = "Jane"), I want to display the result as:

Could you please advise on how this can be achieved using only the QuickSight console without resorting to SQL?

Thank you for your support!

Regards,
Rami

Hi

In QS you can achieve this by joining both of the datasets.

Hi @Shahid_Muhammad ,

I have performed a left outer join between two datasets:

  • car_main (containing car_id and car_name)
  • car_owner (containing car_id and owner_name)

However, instead of returning a single row per car with the owners comma-separated, the join is returning multiple rows — one row for each owner.

Later, when I try to aggregate the data in the analysis table, I only manage to get a count of the owners, but I am unable to display the owners as a comma-separated list.

Could you please advise on how to adjust the join or modify the dataset/analysis so that I can return just one row per car with the owners displayed as a comma-separated string?

Thank you in advance for your assistance.

Regards,

Replying to this thread as it might help other users searching for a solutions for a similar topic, is order to resolve this I had to manage it in the following ways

  • Create a dataset that join information from two other existing datasets
  • Created a left outer join to get the data needed
  • In the Analysis created a pivot table and used in the format a tabular format which resolved my issue
1 Like