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

dmurgit1

GrantSmith

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

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

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

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

jroach

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