Split string with certain delimiters and make a new row each element (each element will further split in to columns).

[#1]

Hi, I am new to  jmp scripting. Trying to automate split "Data" column (Input.jmp) each cell value in to individual chunks of strings using space as the delimiter and each individual chuck of string will make a new row with including row A,B,C,D,E (Output_intermediate.jmp). For the final output (Output_Final.jmp), need to further split string "Data_Coulmn_split_by_space_delimiter" to it's individual components using ":" as delimiter and an additional column (:Data1) with the 1st element first letter. I used the intermediate data set to explain how the original string needs to split and processed, not necessary to follow that order as long as result is equivalent to Output_Final.jmp. Please kindly help me. Thanks in advance !

[#2]

Attaching Output_Final.jmp

[#3 · Accepted solution]

Here is a script that will create the data table you are asking for, with one exception.  I am at a loss on how to construct your column E.

Names Default To Here( 1 );
dt = Data Table( "Input" );
dtOut = New Table( "Final Output",
	New Column( "A" ),
	New Column( "B", character ),
	New Column( "C" ),
	New Column( "D" ),
	New Column( "Data_1", character ),
	New Column( "Data_2", character ),
	New Column( "Data_3", character ),
	New Column( "Data_4", character ),
	New Column( "Data_5", character ),
	New Column( "Data_6", character ), 

);
For( i = 1, i <= N Rows( dt ), i++,
	If( i == 1,
		theRow = 0;
		theRow = N Rows( dtOut );
	);
	If( dt:Data_Column[i] != "X",
		dtOut << Add Rows( 3 );
		For( k = 1, k <= 3, k++,
			theRow++;
			dtOut:A[theRow] = dt:A[i];
			dtOut:B[theRow] = dt:B[i];
			dtOut:C[theRow] = dt:C[i];
			dtOut:D[theRow] = dt:D[i];
			dtOut:Data_1[theRow] = Substr( Word( k, dt:Data_Column[i], " " ), 1, 1 );
			dtOut:Data_2[theRow] = Substr( Word( 1, Word( k, dt:Data_Column[i], " " ), ":" ), 2 );
			dtOut:Data_3[theRow] = Word( 2, Word( k, dt:Data_Column[i], " " ), ":" );
			dtOut:Data_4[theRow] = Word( 3, Word( k, dt:Data_Column[i], " " ), ":" );
			dtOut:Data_5[theRow] = Word( 4, Word( k, dt:Data_Column[i], " " ), ":" );
			dtOut:Data_6[theRow] = Word( 5, Word( k, dt:Data_Column[i], " " ), ":" );
		);
	);
);

 P.S.   You have a typo in the data table column name called Data_Column.......it is incorrectly spelled Data_Coulmn

[#4]

Hi txnelson, it works perfect !
Regarding the column E - it is just need to be copied from the original table. I have added in "New Column( "E", character )," & "dtOut:E[theRow] = dt:E[i];" to copy column E as well. Really appreciate it, thanks alot !