Split values from a field and create rows for each value

Question & Answer
1

Hi team, I have the following situation. I have a group of details that are assigned to some tickets.

image

These details are concatenated in the same row as in this example.

image

I need to split this up and create a new row for each detail in order to count them and make several calculations.

image

Initially I tried the split function but it only returns one value. Also, if the split function works, another of the problems is the common denominator that I should use, since I could use the comma but as you can see, the details like “Customer request, changes needed” contain a comma within the text which would divide incorrectly the string.

I searched for similar questions (a, b), but couldn’t find a solution to this although it seems to be a fairly common problem that other users might have.

So, I would like to know two things, 1) if there is a common denominator that can be used (for example a comma) how would this problem be solved, 2) and if the common denominator doesn’t work, how can I split this string (I guess the substring function may work somehow).

2

This is something that cannot be accomplished with a calculated field. Calculated fields are only calculations that happen at the row level (i.e they can’t create rows).

If you are using athena, you can run a split and cross join.

https://prestodb.io/docs/current/functions/string.html

https://prestodb.io/docs/current/sql/select.html#cross-join

1 Like