Exclude null values from Group Concat in Magic ETL

brooke_warner
brooke_warner Member
in Magic ETL

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?

  1. Step 1: create custom columns
    1. "CASE WHEN flag='true' THEN nullif(id ,'') ELSE null END AS flagged_ids
  2. Step 2: group by
    1. "GROUP_CONCAT(`flagged_ids` SEPARATOR ', ') AS "flagged_ids_list"
Tagged:

Best Answer

  • rco
    rco Domo Employee
    Answer ✓

    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, '^, ', '')

    image.png


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

    Randall Oveson <randall.oveson@domo.com>

Answers

  • ArborRose
    ArborRose Coach

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • rco
    rco Domo Employee

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

    image.png

    Result:

    image.png

    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, '') ?

    Randall Oveson <randall.oveson@domo.com>

  • brooke_warner
    brooke_warner Member

    @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".

    image.png image.png image.png
  • rco
    rco Domo Employee
    Answer ✓

    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, '^, ', '')

    image.png


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

    Randall Oveson <randall.oveson@domo.com>

  • rco
    rco Domo Employee

    The bug has now been fixed.

    Randall Oveson <randall.oveson@domo.com>

  • brooke_warner
    brooke_warner Member

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

Categories