Exclude null values from Group Concat in Magic ETL

brooke_warner · May 22

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
1. "CASE WHEN flag='true' THEN nullif(id ,'') ELSE null END AS flagged_ids
Step 2: group by
1. "GROUP_CONCAT(`flagged_ids` SEPARATOR ', ') AS "flagged_ids_list"

rco · May 22

Thank you for the additional detail. There is a bug here after all! While nulls are being ignored in most cases, if the first null value in a group is null, it is generating a separator. I'll make sure this gets fixed as soon as possible.

In the meantime, we can at least trust that these unexpected separators will only occur at the beginning of the string. That makes it relatively easy to post-process the columns in a way that eliminates the extraneous separator:

REGEXP_REPLACE(Hedge_ISIN, '^, ', '')

I'll update this forum thread when a fix has been released. Thanks again for pointing this out clearly.

ArborRose · May 22

It's probably treating NULL and '' (empty string) differently. Even if you use NULLIF(id, '') , the value might get a leading separator. Thus flagged_ids still triggers the separator with a valid ID.

['', 'BE0000344532'] becomes ", BE0000344532"

You could try skipping the nulls with a filter instead of relying on group_concat.

rco · May 22

Magic ETL's GROUP_CONCAT does ignore nulls, as is the case in MySQL. You can test this using the SQL tile:

Result:

Is it possible that there are values in id that are blank but not equal to the empty string? What happens if you do NULLIF(SQUASH_WHITESPACE(id), '') rather than just NULLIF(id, '') ?

brooke_warner · May 22

@rco Thank you for the example! I tried using the SQL tile instead of the "group by", but am still receiving the below results. My flags are "hedge" and "benchmark". My IDs are "CUSIP" or "ISIN" or "ticker".

rco · May 22

Thank you for the additional detail. There is a bug here after all! While nulls are being ignored in most cases, if the first null value in a group is null, it is generating a separator. I'll make sure this gets fixed as soon as possible.

In the meantime, we can at least trust that these unexpected separators will only occur at the beginning of the string. That makes it relatively easy to post-process the columns in a way that eliminates the extraneous separator:

REGEXP_REPLACE(Hedge_ISIN, '^, ', '')

I'll update this forum thread when a fix has been released. Thanks again for pointing this out clearly.

rco · May 23

The bug has now been fixed.

brooke_warner · May 23

Thank you so much! I'm seeing now that the bug is fixed :)

Exclude null values from Group Concat in Magic ETL

Best Answer

Answers

Categories