Carlos . Posted March 17, 2023 Share Posted March 17, 2023 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! Link to comment Share on other sites More sharing options...
Gaia Paolini Posted March 20, 2023 Share Posted March 20, 2023 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 More sharing options...
Carlos . Posted March 20, 2023 Author Share Posted March 20, 2023 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now