Merge value of a column based on same rows of another column

[musama]

Hey Community,

Hope you all are doing fine. I am having trouble creating a table where we have to merge value of a column based on same rows of another column. I am shaing ss below:

I want to create new row based on all this data as:
Jun 9, 2024 westlink combined 92

How can I accumplish this inside quicksight?

Regards

[Xclipse]

Hi @musama, to achieve the desired result, you can use the “Calculated Field” feature to combine the values of the avg_time_to_answer_seconds for rows with the same WeekEnding and prefix WESTLink in the queuename column.

Here’s is an approach, you can try:

1. Create a Calculated Field:

   • Go to your analysis in Amazon QuickSight.
   • Click on the dataset.
   • Click on “Add” and then “Add calculated field”.

2. Define the Calculated Field:

   • Name the calculated field (e.g., WESTLink Combined).

   • Use the following formula to sum the avg_time_to_answer_seconds for all rows where the queuename contains ‘WESTLink’:

     ifelse(contains({queuename}, 'WESTLink'), {avg_time_to_answer_seconds}, 0)
     

3. Aggregate the Calculated Field:

   • Now, you need to sum this calculated field for each WeekEnding.

   • Create another calculated field to sum the values:

     sumOver(ifelse(contains({queuename}, 'WESTLink'), {avg_time_to_answer_seconds}, 0), [{WeekEnding}])
     

4. Create a New Table:

   • Go to your analysis view and add a new table visual.
   • Add WeekEnding to the Rows field.
   • Add the sum of the calculated field (WESTLink Combined) to the Values field.

5. Filter and Format:

   • You might want to filter the table to only show the specific rows you’re interested in.
   • Format the table as needed to achieve your desired look.

Here’s a step-by-step example based on your data, this is a pseudocode, refer to QS documentation for actual syntax - leverage Amazon Q on the documentation page:

1. Add Calculated Field:

   • Name: WESTLink_Time
   • Formula: ifelse(contains({queuename}, 'WESTLink'), {avg_time_to_answer_seconds}, 0)

2. Aggregate the Calculated Field:

   • Name: WESTLink_Combined
   • Formula: sumOver({WESTLink_Time}, [{WeekEnding}])

3. Build the Table:

   • Rows: WeekEnding
   • Values: sum(WESTLink_Combined)

This will result in a table that sums up the avg_time_to_answer_seconds for all queues containing ‘WESTLink’ for each WeekEnding.

Hope this gives you a general understanding of how to go about solving this problem. Also look into the QuickSight functions using Amazon Q to help you further.

[Xclipse]

Hi @musama, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!