Clean Up Category Axis

[Carlos .]

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.

This 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!

[Gaia Paolini]

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],...

[Carlos .]

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.