Count distinct values in cells

Hey,

I currently have a dataset where one column contains multiple values as shown below:

Having this set of data I would need to have this result :
Defect type COUNT
N/A 2
RELIURE CENTREE 3
HAUTEUR DE LA FACE 3
RELIURE DROITE 2
HAUTEUR DU DOS 3
COUPE CARREE 2
SKI SLOPE 2

In the end, the goal is to display a bar chart with defect type as X axis and the count as Y axis. Thanks for the support.

Hi @Sarah_Letailleur ,

Welcome to the QuickSight Community!!

I understand your requirement, but since we will need to flatten data to be able to produce the visual, you will need to prepare the data in the backend.

I can think of 2 options:

  1. flatten the data in backend - this may create reduplication for records and may not be ideal to be used for other analysis
  2. flatten only the defect column to fetch a probable list of all defects and then join this table in the backend as the left table with the base table as right table to calculate the metrics you are looking for using conditional check.

Thanks,
Prantika

Hi @Sarah_Letailleur -

The QuickSight team recently added a powerhouse of a feature Amazon QuickSight launches Highcharts visual (preview)

You can solve this with Highcharts. Here is an example to get you started.

count-distinct-values-in-cells/40420

2 Likes

Hi @Sarah_Letailleur,
It’s been awhile since we last heard from you, did you have any additional questions regarding your post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @robdhondt @Brett, sorry for the late reply, I was in vacation and not able to check the solution. Looks like @robdhondt solution is fitting quite well with the situation. Thanks a lot for your support!

2 Likes