Using Magic ETL, how can i sort an aggregated string?
Answers
-
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!**1 -
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.
0 -
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!**1 -
@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"0
