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

dmurgit1
dmurgit1 βšͺ️


Tagged:

Answers

  • GrantSmith
    GrantSmith Indiana πŸ”΄

    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.

  • dmurgit1
    dmurgit1 βšͺ️

    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 Indiana πŸ”΄

    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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

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