Distinct List_Agg

NateBI

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,

MichelleH

@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

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 '/')

MichelleH

@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

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

MichelleH

@NateBI What does the error say?

NateBI

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

MichelleH

@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

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

MichelleH

@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

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 '/')

NateBI

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