I want to use jsl to split data in column "result" with delimiter "|" to new column "PIN" and "value". Kindly help, thank you in advance
Please refer my attachment
new table
Given the data table
This JSL will produce the required new table
names default to here(1); // Create a handle to point to the data table in question dt = current data table(); // Convert the Result column into several columns // Text to columns dt << Text to Columns( columns( :Result ), Delimiters( "|" ) ); // Use a multiple series stack to stack the data dtStack = dt << Stack( columns( :Result 2, :Result 3, :Result 4, :Result 5, :Result 6, :Result 7 ), Source Label Column( "Label" ), Stacked Data Column( "Data" ), "Non-stacked columns"n( Keep( :Unit#, :Result 1 ) ), Number of Series( 2 ), Output Table( "Untitled 20.jmp" ) ); // Delete unneeded columns dtStack << delete columns(:Label,:Label2); // Rename the stacked columns dtStack:Result 1 << set name ("Number Of Tests"); dtStack:Data << set name("PIN"); dtStack:Data2 << set name("Value");
All of the above JSL was created by running the Text to Columns, Stack and Column name changes etc. interactively, and then going to the JMP log and cutting and pasting the JSL that JMP produced into a script window. I will confess to some cleaning up of cut and pasted JSL to make it look better.
// Get all of the column names resultList = dt << get column names(); // Only the columns Result 2 thru Result N are to be // stacked, so remove the first 3 columns from the list remove from(resultList,1,3); // Use a multiple series stack to stack the data dtStack = dt << Stack( columns( resultList ), Source Label Column( "Label" ), Stacked Data Column( "Data" ), "Non-stacked columns"n( Keep( :Unit#, :Result 1 ) ), Number of Series( 2 ), Output Table( "Untitled 20.jmp" ) );
Split a Data Table Column into Multiple Columns (jmp.com)
Select your column
go to columns menu
set your delimilter
press OK and you have your result
copy script from enhanced log / workflow and modify it as needed
// Text to columns Data Table("Sheet1") << Text to Columns(columns(:Result), Delimiters("|"));
thank you a lot
thanks Jim so much, it is very good solution
// Use a multiple series stack to stack the data dtStack = dt << Stack( columns( :Result 2, :Result 3, :Result 4, :Result 5, :Result 6, :Result 7 ),
in the step above, If my dataset return so many result up to result 120, and it variance may be more than 120 result, do we have any way to code jsl script to stack data Jim?
