GROUP_CONCAT function in Dataset View vs MySQL Dataflow

ARosser
ARosser Member
in APIs & Domo Developer

I have created a MySQL Dataflow where I was able to successfully use the GROUP_CONCAT function. It is preferred that I use a Dataset View in this circumstance since the underlying data is coming from AppDB and therefore updates every 15 minutes if there is new data. I have copied and pasted the exact query that is successfully running for the MySQL Dataflow. I have updated the dataset names to the aliases that the View uses. But I am getting an error. Of course the errors in SQL Editor for views are completely useless. I assume it is due to the GROUP_CONCAT function. Is it not supported for views? Below is the query I have successfully running for the MySQL Dataflow

SELECT
RECORDNO,
Location ID (ARDetail),
Department ID (ARDetail),
Customer ID (ARRecord),
Invoice Number,
GROUP_CONCAT(
CONCAT(date,' - ',IFNULL(Name,user),': ',postBody)
ORDER BY DATE(date) DESC
SEPARATOR '\r\n') AS Notes,
MAX(date) AS Most Recent
FROM collection_notes_dataset
LEFT JOIN users
ON collection_notes_dataset.user = users.User ID
GROUP BY
RECORDNO,
Location ID (ARDetail),
Department ID (ARDetail),
Customer ID (ARRecord),
Invoice Number

Tagged:

Best Answer

  • Sean_Tully
    Sean_Tully Contributor
    edited June 3 Answer ✓

    The "Add Column" group by option for combining strings uses the comma as a separator, but the "Add Formula" option allows you to use the group_concat formula you've already written.

Answers

  • Sean_Tully
    Sean_Tully Contributor

    AFAIK dataset views do not have this functionality.

    I do believe you can do this in Magic ETL using a Group By formula, which might be fast enough for your needs depending on the size of your inputs.

  • ARosser
    ARosser Member

    Unfortunately the only option for a separator is a comma. The test I am separating already has commas and therefore it makes it less legible. Using the MySQL Dataflow I am able to use \r\n which acts as a line break. Thanks!

  • Sean_Tully
    Sean_Tully Contributor
    edited June 3 Answer ✓

    The "Add Column" group by option for combining strings uses the comma as a separator, but the "Add Formula" option allows you to use the group_concat formula you've already written.

Categories