Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
OneNorthJMP
Level IV

Summary of Different Test Number with Same Test Name

I am trying to do a summary of different test number with same test name from Table 1 to Table 2. Is there a quick and dirty way in JMP to achieve this? Thanks

 

Table 1

testnumtestname
T17561Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17562Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17563Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17564Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17565Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17566Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17567Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB
T17621Ana_PowerRegulator_ATE_Chara@LA
T17622Ana_PowerRegulator_ATE_Chara@LA
T17623Ana_PowerRegulator_ATE_Chara@LA
T17624Ana_PowerRegulator_ATE_Chara@LA

 

Table 2

testnametestnum
Ana_Limiter_vdhf_ATE_Chara_LA@LA-LBT17561,T17562,T17563,T17564,T17565,T17566,T17567
Ana_PowerRegulator_ATE_Chara@LAT17621,T17622,T17623,T17624
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Summary of Different Test Number with Same Test Name

The JSL I provided will work for any number of testnames and test numbers

Names Default To Here( 1 );
dt = New Table( "Example", New Column( "testname", character ), New Column( "testnum", character ) );
For( i = 1, i <= 50, i++,
	tnam = "Testname" || Char( i );
	kstart = Random integer( 1, 5 );
	For( k = kstart, k <= Random integer( 1, 25 + kstart ), k++,
		dt << add rows( 1 );
		dt:testname[N Rows( dt )] = tnam;
		dt:testnum[N Rows( dt )] = "T" || Char( k );
	);
);

// Create the new summary data table
dtTrans = dt << Transpose(
	columns( :testnum ),
	By( :testname ),
	Output Table( "Transpose" )
);

// Get rid of the not needed column called "Label"
dtTrans << delete columns("Label");

// Get a list of the columns in the data table
colNames = dtTrans << get column names(string);
// Get rid of the first column so the list only contains the transposed columns
colNames = remove(colNames,1,1);

// Create the new testnum column
dtTrans << New Column( "testnum", character );

// Loop through all of the rows in the table and create the concatenated column
For( theRow = 1, theRow <= N Rows( dtTrans ), theRow++,
	:testnum[theRow] = :Row 1[theRow];
	For( theCol = 2, theCol <= N Items( colNames ), theCol++,
		If( Column( colNames[theCol] )[theRow] != "",
			:testnum[theRow] = :testnum[theRow] || ", " || Char( Column( colNames[theCol] )[theRow] )
		)
	);
);

// Delete the unnecessary columns
dtTrans << delete columns( colNames );

testname.PNG

 

Jim

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: Summary of Different Test Number with Same Test Name

I am not exactly sure what you are asking for.  But, if you want to create your table 2, from the table 1, here is a script that will do that

names default to here(1);

// Create the sample table
dt = New Table( "Example",
	Add Rows( 11 ),
	New Script(
		"Source",
		Data Table( "Untitled 13" ) << Split(
			Split By( :Column 2 ),
			Split( :Column 1 ),
			Sort by Column Property
		)
	),
	New Column( "testnum",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"T17561", "T17562", "T17563", "T17564", "T17565", "T17566", "T17567",
			"T17621", "T17622", "T17623", "T17624"}
		),
		Set Display Width( 68 )
	),
	New Column( "testname",
		Character,
		"Nominal",
		Set Values(
			{"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB",
			"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB",
			"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB",
			"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB",
			"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB",
			"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB",
			"Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB", "Ana_PowerRegulator_ATE_Chara@LA",
			"Ana_PowerRegulator_ATE_Chara@LA", "Ana_PowerRegulator_ATE_Chara@LA",
			"Ana_PowerRegulator_ATE_Chara@LA"}
		),
		Set Display Width( 244 )
	)
); // End of Example table creation

// Create the new summary data table
dtTrans = dt << Transpose(
	columns( :testnum ),
	By( :testname ),
	Output Table( "Transpose" )
);

// Get rid of the not needed column called "Label"
dtTrans << delete columns("Label");

// Get a list of the columns in the data table
colNames = dtTrans << get column names(string);
// Get rid of the first column so the list only contains the transposed columns
colNames = remove(colNames,1,1);

// Create the new testnum column
dtTrans << New Column( "testnum", character );

// Loop through all of the rows in the table and create the concatenated column
For( theRow = 1, theRow <= N Rows( dtTrans ), theRow++,
	:testnum[theRow] = :Row 1[theRow];
	For( theCol = 2, theCol <= N Items( colNames ), theCol++,
		If( Column( colNames[theCol] )[theRow] != "",
			:testnum[theRow] = :testnum[theRow] || ", " || Char( Column( colNames[theCol] )[theRow] )
		)
	);
);

// Delete the unnecessary columns
dtTrans << delete columns( colNames );

If what you want is something different, would you please provide more detail on exactly what you want

Jim
Highlighted
OneNorthJMP
Level IV

Re: Summary of Different Test Number with Same Test Name

Hi Jim,

Thanks for reply. But unfortunately it is not what i am looking for. The table1 & 2 is just a simple example. But actual table is much bigger than that.

The objective is simple. The original input table, I have 2 columns, 1 is test number, 1 is test name. There are many test names are using different test number like i show in the Table1. I want to do a quick summary by using JMP to tabulate into another table like Table2 which will summary all the same test name that have different test number in one glance.

My example above show 1 test name "Ana_Limiter_vdhf_ATE_Chara_LA@LA-LB" have 7 different test number which is "T17561,T17562,T17563,T17564,T17565,T17566,T17567"
Highlighted
txnelson
Super User

Re: Summary of Different Test Number with Same Test Name

The JSL I provided will work for any number of testnames and test numbers

Names Default To Here( 1 );
dt = New Table( "Example", New Column( "testname", character ), New Column( "testnum", character ) );
For( i = 1, i <= 50, i++,
	tnam = "Testname" || Char( i );
	kstart = Random integer( 1, 5 );
	For( k = kstart, k <= Random integer( 1, 25 + kstart ), k++,
		dt << add rows( 1 );
		dt:testname[N Rows( dt )] = tnam;
		dt:testnum[N Rows( dt )] = "T" || Char( k );
	);
);

// Create the new summary data table
dtTrans = dt << Transpose(
	columns( :testnum ),
	By( :testname ),
	Output Table( "Transpose" )
);

// Get rid of the not needed column called "Label"
dtTrans << delete columns("Label");

// Get a list of the columns in the data table
colNames = dtTrans << get column names(string);
// Get rid of the first column so the list only contains the transposed columns
colNames = remove(colNames,1,1);

// Create the new testnum column
dtTrans << New Column( "testnum", character );

// Loop through all of the rows in the table and create the concatenated column
For( theRow = 1, theRow <= N Rows( dtTrans ), theRow++,
	:testnum[theRow] = :Row 1[theRow];
	For( theCol = 2, theCol <= N Items( colNames ), theCol++,
		If( Column( colNames[theCol] )[theRow] != "",
			:testnum[theRow] = :testnum[theRow] || ", " || Char( Column( colNames[theCol] )[theRow] )
		)
	);
);

// Delete the unnecessary columns
dtTrans << delete columns( colNames );

testname.PNG

 

Jim

View solution in original post

Highlighted
OneNorthJMP
Level IV

Re: Summary of Different Test Number with Same Test Name

Hi Jim,
Your script work perfectly! It is exactly what i am looking for.
Article Labels

    There are no labels assigned to this post.