How to split a column which has cells with delimited values into rows maintaining their original order

[47e38edcf5b72d47f353]

I have values in column delimited by carriage return and new lines. I have been able to the individual values into rows by extracting the values into an array and converting an array into rows but their original order is lost so when I apply ROWNUMBER the number does not match their position in the delimited cell. Is there a better way to split into rows which will maintain their order or to add their order number to the array and spit this into rows with a column for the original value and the order number?

[AMiller_Tri · Accepted solution]

Hi @Lee Gorringe? , Thanks for your question.

This is a bit confusing, as you're asking for the "original order", but in the "desired format" picture to the right, the order is changed - to it being ordered as ascending.

So I'll try answer both ways.

option a - have it ordered by ascending\descending

As I see it, there are 2 options.

1. use the arraysort() function before performing the flatten() transformation.

2 when creating the rownumber(), sort by the relevant column (remember to aggregate by a common column for each original record)

option b - maintain original order. which isn't necessarily ordered

A. (easy way)use $sourcerownumber metadata parameter as your sort_by field

That way, the rownum will be sorted by the original order of rows, as was created by the flatten() transform. Since the flatten() maintains the original order of how the numbers appeared in the original form,this should do the trick.

B. if, for any reason - this isn't possible (the metadata parameter isn't available) - you can create this:

By using the function:

1. arraylen() (know the length of your original array)
2. range() (create an array that is a range between 1 and the length of your array)
3. arrayzip() (merge between the 2 arrays)

Can be done in 1 step if you're careful: (can copy-paste step, just change column names)

derive type: single value: arrayzip([column2,range(1, arraylen(column2) + 1, 1)]) as: 'column3'

then you can use sortarray() before flattening.

after the flatten - the elements are in their original order, and you got a "rownum" column for free =]

Hope this was helpful,

Amit.

[47e38edcf5b72d47f353]

Many thanks for your quick and very comprehensive answer! It was your third option that I needed but I really appreciate you taking the time to explain all three.

[AMiller_Tri]

Of course @Lee Gorringe? ! This was an excellent question, thank you for raising it up!

Glad I could be of help.