Forum Discussion
Group codes and sum their values from a single text string
Hi,
I've got a text column with this format "Oper A - 8.5, Oper B - 3.0, Oper A - 2.5, Oper C - 5.0, Oper B - 5.0" to define the operations that happened in 24 hours. Basically time is separated by " - " from the operation code and each entry is separated by commas.
What I would like to have at the end is "Oper A - 11.0, Oper B - 8.0, Oper C - 5.0", meaning I would like to group the operation codes and sum their hours.
Anybody could suggest any approach to this? Thanks!
Hi Anonymous
You can split those into their own rows using the same split function. Just click the advanced option in the Split dialog and set something like this for your column.
4 Replies
Hi Anonymous
First of all, try splitting the column into two columns. I suggest doing this in the Query Editor.
Then group them in the Query Editor. This should give you the output you are after.
Thank you for your reply Phil,
That part is easy, but the data I've got is a bit more complex. As I mention in my original post, there're several codes in the same text column, separated by commas, so splitting the columns will result in a variable number of extra columns. To get more context, see the screenshot of the column I'm taking about.
The number of codes could be different per row, and the times too.
Thank you!
Hi Anonymous
You can split those into their own rows using the same split function. Just click the advanced option in the Split dialog and set something like this for your column.