I am trying to use the "group concat" function within the Group By tile in Magic ETL to create a list of IDs that meet some flag condition. If the flag condition is not met, I do not want to include the ID in the concatenated list.
From my understanding of MySQL, if a value is null, it should not be included in the grouped list. However, in Magic ETL, I am getting results like this, where the blank row inserts a comma separator before the answer:
", BE0000344532"
Using this formula works in the SQL Dataflow transformer, but none of the methods I have tried in Magic ETL have gotten rid of the above issue. Does the group concat formula work differently in Magic ETL? Is this a bug?
- Step 1: create custom columns
- "CASE WHEN
flag
='true' THEN nullif(id
,'') ELSE null END AS flagged_ids
- Step 2: group by
- "GROUP_CONCAT(`flagged_ids` SEPARATOR ', ') AS "flagged_ids_list"