Concatenate in Magic ETL

rupinderkaur87
rupinderkaur87 Member
in Magic ETL

i have data like this :

image.png

And with magic ETL want to concatenate contents of column C for same values in Column A.

Desired result:

image.png

Result with Group by's concatenate

image.png
image.png

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 2023 Answer ✓

    Use the group by tile, set Member Number as the Grouping field and then select Add Formula and use something like the following:

    GROUP_CONCAT(DISTINCT
    CASE WHEN ` 3rd Party` IS NOT NULL
    THEN ` 3rd Party` END)

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You likely have some blank spaces in your 3rd party field. To handle these you can ignore empty fields:

    GROUP_CONCAT(DISTINCT
CASE WHEN TRIM(COALESCE(`3rd Party`, '')) <> ''
THEN ` 3rd Party` END)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited November 2023 Answer ✓

    Use the group by tile, set Member Number as the Grouping field and then select Add Formula and use something like the following:

    GROUP_CONCAT(DISTINCT
    CASE WHEN ` 3rd Party` IS NOT NULL
    THEN ` 3rd Party` END)

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You likely have some blank spaces in your 3rd party field. To handle these you can ignore empty fields:

    GROUP_CONCAT(DISTINCT
CASE WHEN TRIM(COALESCE(`3rd Party`, '')) <> ''
THEN ` 3rd Party` END)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • rupinderkaur87
    rupinderkaur87 Member

    Thanks @GrantSmith and @ColemenWilson for the solutions.

Categories