How to match combination string which is not in same order in Looker?

Question

I've a column named country combination beside which total count column states the count of how many times that country combination has appeared. I'm trying to create a pie chart in Looker to see overall occurrence.

country combination	total count
Canada - India	11
India - Canada	25

The country pair appears 2 times because of the order, I want to merge it to one country pair so at the end it becomes Canada-India and total count becomes 36(11+25)

There are several such country pairs in the table, how can I join that to one unique pair to see pie chart without duplication.

I tried Looker's Group option in custom field but there manually I've to group all combinations which is not possible because combinations keeps on changing and there are innumerable pairs. How can we club them in Lookml or Looker?

Answer 1

You should create a dedicated country_combination_ordered dimension where you reorder the countries. Its sql parameter would be:

  ( 
    SELECT
      STRING_AGG(country_combinations, " - " ORDER BY country_combinations ASC)
      FROM UNNEST(split(country_combination, ' - '))  AS country_combinations
  )

You can test the result with the following query:

WITH Words AS
 (SELECT 'Canada - India' as country_combination, 11 as total_count UNION ALL
  SELECT 'India - Canada', 25)
SELECT
  ( 
    SELECT
      STRING_AGG(country_combinations, " - " ORDER BY country_combinations ASC)
      FROM UNNEST(split(country_combination, ' - '))  AS country_combinations
  ) AS country_combination_ordered,
  SUM(total_count) AS total_count
FROM Words
GROUP BY 1