Distinct List_Agg

NateBI
NateBI Contributor
in Magic ETL

Hi Folks,

In Magic ETL, I'm grouping by a key and defining a new column using the formula:

List_Agg(customer_name, ' / ')

Is there a method to generate a distinct list of customer names by key? I don't want the repeat customer names in this value.

All support appreciated

Kind regards,

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @NateBI It looks like it MagicETL may not support LISTAGG(DISTINCT), even though it is a valid SQL statement. As a work around, I'd suggest creating a separate branch of your ETL before the Group By that filters out duplicate values according to all your group by fields and RESOURCE_NAME. Then you can run a parallel Group By tile with the LISTAGG function (without Distinct), and join that back to your original flow.

  • rco
    rco Contributor
    Answer ✓

    GROUP_CONCAT, which is identical in behavior but different in syntax to LISTAGG, does support the DISTINCT keyword as of relatively recently: GROUP_CONCAT(DISTINCT customer_name SEPARATOR '/')

    Randall Oveson <randall.oveson@domo.com>

Answers

  • MichelleH
    MichelleH Coach

    @NateBI Yes, you can add DISTINCT inside the list_agg function the same as you would to for a distinct count like this:

    List_Agg(DISTINCTcustomer_name, ' / ')

  • NateBI
    NateBI Contributor

    Hi @MichelleH this seems to throw an error for me.

  • MichelleH
    MichelleH Coach

    @NateBI What does the error say?

  • NateBI
    NateBI Contributor
    edited April 2023

    @MichelleH it's a Syntax Error:
    I've also tried DISTINCT LISTAGG(Resource_Name, ' / ')

    image.png
  • MichelleH
    MichelleH Coach

    @NateBI Are you using a Formula Tile or a Group by tile? The LISTAGG function will not work in the Formula Tile because it is an aggregate.

  • NateBI
    NateBI Contributor

    @MichelleH Yeah, this is in a Group By tile but with the formula column feature

  • MichelleH
    MichelleH Coach
    Answer ✓

    @NateBI It looks like it MagicETL may not support LISTAGG(DISTINCT), even though it is a valid SQL statement. As a work around, I'd suggest creating a separate branch of your ETL before the Group By that filters out duplicate values according to all your group by fields and RESOURCE_NAME. Then you can run a parallel Group By tile with the LISTAGG function (without Distinct), and join that back to your original flow.

  • rco
    rco Contributor
    Answer ✓

    GROUP_CONCAT, which is identical in behavior but different in syntax to LISTAGG, does support the DISTINCT keyword as of relatively recently: GROUP_CONCAT(DISTINCT customer_name SEPARATOR '/')

    Randall Oveson <randall.oveson@domo.com>

  • NateBI
    NateBI Contributor

    @MichelleH Thank you, this workaround would have been the second go-to but @rco this worked for me. Thanks, both!

Categories