Using Magic ETL, how can i sort an aggregated string?

dmurgit1
dmurgit1 Member
in Magic ETL


Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @dmurgit1

    You can't simply sort the aggregated string. You'd need to split your string into separate column then pivot those columns into rows, sort then and then reassemble them using the reverse process.

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

    Sorry I should have been a bit more specific. I have a dataset I am aggregating. I have rows of text field that I want to aggregate separating by , into one row. The aggregate works, however the values are in a random order. I was hoping to sort the dataset prior to aggregating and would force the aggregation to be in some kind of order.

  • GrantSmith
    GrantSmith Coach

    The group by function doesn't allow you to sort your records when you're concatenating them together with a comma. You could try to use a Rank & Window tile before the group by to sort the values and calculate the row number, then attempting to do the group by on the sorted data.

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

    @dmurgit1 unfortunately what you're asking for is not reliably acchievable in Magic 2.0

    You must use MySQL (user defined variables) or Redshift (LISTAGG) to accomplish what you're describing.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jroach
    jroach Member
    edited January 2023

    Any update to this?

    The concat aggregated strings is really useful for evaluating groups of items (in our case people)

    But the order is not significant. So I would only want to retain one of these.

    Liz, Jack, Jenna

    Jack, Jenna, Liz

Categories