Add ORDER BY to GROUP_CONCAT Function in Magic ETL v2
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:
Was this comment helpful? Click Agree or Like below.
Did this comment solve your problem? Accept it as the solution!
Comments
-
ORDER BY inside GROUP_CONCAT() should be supported everywhere GROUP_CONCAT could appear. I mention that because it should be allowed in the Group By tile, but it should be supported in the SQL Tile as well, which is in Beta right now, but neither tile allows this currently. Since SQL Tile is in Beta, maybe it makes sense to enable it there first ¯\_(ツ)_/¯
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive