Jump to content

Clean Up Category Axis

Carlos .

Recommended Posts

Hello, I'll explain with pictures below what I'm trying to do.

Column 5.2 has several pieces of data that are delimited with semicolons. I need to plot each one and count it, but I figure it's best to split them up into multiple columns first. This is what it looks like before splitting them up.


Data structure after split. Column 5.2 turned into 5 columns named 5.2.1, 5.2.2,....5.2.5.


The problem is how to plot the data after the split without having this grouping problem in the Category axis.


I want it to look more like this.

image.thumb.png.08ae1f31fd785db8cd028ca3d8db6902.pngThis data structure for this chart above is different, however. Each row only contains 1 piece of information, not multiple delimited ones.


Lastly, this is not something I want to do in Excel since I want the sheet to go unmodified straight into Spotfire. Transformations after it is loaded would be fine, however.

Any help is appreciated!

Link to comment
Share on other sites

if there are always up to (say) 5 different pieces in Column 5.2, the easiest thing would be to apply 5 separate transformations to split it, then an unpivot. You can do it in the data canvas.

Calculate new columns: [Column 5.2.1], [Column 5.2.2],..., as Split([Column 5.2],';',1), Split([Column 5.2],';',2), ...

Then add Unpivot transformation, pass through all the other columns and transform [Column 5.2.1], [Column 5.2.2],...

Link to comment
Share on other sites

Thank you Gaia! I got it working.

For anybody wondering how it's supposed to look like, see the images below.

Everything is done in Canvas mode. First use this code to split, changing the last number from 1-5:

Split([5.2 : Failure Event Type / Source of Incident : Select all that describe or were contributing factors to the incident/event],"; ",1)

Then do the Unpivot afterward.


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...