Add ORDER BY to GROUP_CONCAT Function in Magic ETL v2

brycec

Currently, Magic ETL v2 supports the aggregate function GROUP_CONCAT in its Group By tile. While clauses inside the GROUP_CONCAT function like DISTINCT and SEPARATOR are available, the ORDER BY clause doesn't work and causes the tile to fail. SQL DataFlows currently support using the GROUP_CONCAT function with the ORDER BY clause.

An example SQL statement would look like this:
GROUP_CONCAT(DISTINCT `Workshop` SEPARATOR ', ' ORDER BY `Workshop`)

Interestingly, the validate button inside the editor validates successfully when using the ORDER BY clause, but outside the SQL editor, the whole Group By tile will fail with the error "Syntax error in expression at character 48 on line 1: GROUP_CONCAT(DISTINCT `Workshop` SEPARATOR ', ' ORDER BY `Workshop`)", with character 48 being the beginning of the ORDER BY clause.

Because of the successful validation, I reached out to Domo Support first to see if this was just a bug and that the ORDER BY clause was supposed to work. They unfortunately confirmed that the ORDER BY clause inside the GROUP_CONCAT function is not supported in Magic ETL v2. They also offered the alternative to use the Rank & Window tile to order by the column before using the Group By tile. I was already aware of this alternative and have used it a number of times. While it works great in some situations where you only need to aggregate and order by one column, the ORDER BY clause inside the GROUP_CONCAT function would be extremely useful when needing to do this for multiple columns at the same time. Otherwise, you would have to split your data into each column, using the Rank & Window tile and Group By tile separately for each column, then join them together again, which is incredibly tedious and messy. And ordering a column before using GROUP_CONCAT is essential to filtering in Domo Cards, otherwise the same values would not combine. For example, the values of "New" and "Old" could be combined on one row as "New, Old" and on another row as "Old, New". This would cause a filter to have both values, and only filter to the rows of one of those selected, even though they are actually the same value, just ordered differently.

You can read more about the GROUP_CONCAT function of MySQL here: https://www.mysqltutorial.org/mysql-aggregate-functions/mysql-group_concat-function/