cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
SpannerHead
Level IV

Concatenating Unique and Nonmissing Data

I have a data table describing the tool history for a series of semiconductor lots.  In some instances, the lots can split and be run across different tools for the same processing step.  In such case, I would like to create a column with each tool concatenated to indicate that > 1 tool was involved.  With a lot of help, I have gotten to the point where I can identify the lots, steps and substeps where > 1 tool was used.  I can transpose the data to get to the tools being nominated in columns but it's still clumsy.  Can I somehow concatenate that to get the unique tools listed.  I also looked at Split but all of the options are not reflected.

 

SpannerHead_1-1734386954050.png

 

 


Slán



SpannerHead
2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Concatenating Unique and Nonmissing Data

Here is one way to do it

As Constant(
	dt = Current Data Table();
	start = 5;
	stop = N Cols( dt );
);
Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" );

As a strong suggestion, please take the time to go to 

     Help=>JMP Online Help

and read the section on Scripting Guide to familiarize yourself with JSL and scripting

Jim

View solution in original post

txnelson
Super User

Re: Concatenating Unique and Nonmissing Data

You are going to a lot of work to get your result.  Here is a simple modification to the last formula that I created that removes the preceding "/"

New Column( "Result",
	Character,
	"Nominal",
	set each value(
		As Constant(
			dt = Current Data Table();
			start = 5;
			stop = N Cols( dt );
		);
		Substr(
			Char( Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" ) ),
			2
		);
	)
);

Here is a complete example with a sample data table

Names Default To Here( 1 );

// Create a sample data table
New Table( "Sample",
	Add Rows( 3 ),
	Set Header Height( 46 ),
	New Column( "lot",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1324567, 1324567, 1324567] )
	),
	New Column( "step", Character, "Nominal", Set Values( {"Material", "Material", "Material"} ) ),
	New Column( "substep", Character, "Nominal", Set Values( {"Deposit", "CD Measure", "Measure"} ) ),
	New Column( "Label", Character, "Nominal", Set Values( {"machine", "machine", "machine"} ) ),
	New Column( "Row 1", Character, "Nominal", Set Values( {"Machine A", "Caliper 1", "Tapemeasure 1"} ) ),
	New Column( "Row 2", Character, "Nominal", Set Values( {"", "Caliper 2", ""} ) ),
	New Column( "Row 2 2", Character, "Nominal", Set Values( {"Machine A", "", ""} ) ),
	New Column( "Row 4", Character, "Nominal", Set Values( {"", "", "Tapemeasure 3"} ) ),
	New Column( "Row 5", Character, "Nominal", Set Values( {"Machine B", "", ""} ) ),
	New Column( "Row 6", Character, "Nominal", Set Values( {"", "", ""} ) ),
	New Column( "Row 7", Character, "Nominal", Set Values( {"", "", ""} ) )
);
	
// Add the concatenated column, with the "/" removed from begining	
New Column( "Result",
	Character,
	"Nominal",
	set each value(
		As Constant(
			dt = Current Data Table();
			start = 5;
			stop = N Cols( dt );
		);
		Substr(
			Char( Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" ) ),
			2
		);
	)
);

Jim

View solution in original post

6 REPLIES 6
txnelson
Super User

Re: Concatenating Unique and Nonmissing Data

Here is a formula that will work to create your Desired Result column

As Constant( dt = Current Data Table() );
Concat Items( Associative Array( dt[Row(), 0] ) << get keys, "/" );
Jim
SpannerHead
Level IV

Re: Concatenating Unique and Nonmissing Data

txnelson

 

This is a huge step forward.  Is there an easy way to dodge the first 4 columns from the equation?  Columns 5 onwards have the relevant data.


Slán



SpannerHead
txnelson
Super User

Re: Concatenating Unique and Nonmissing Data

Here is one way to do it

As Constant(
	dt = Current Data Table();
	start = 5;
	stop = N Cols( dt );
);
Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" );

As a strong suggestion, please take the time to go to 

     Help=>JMP Online Help

and read the section on Scripting Guide to familiarize yourself with JSL and scripting

Jim
SpannerHead
Level IV

Re: Concatenating Unique and Nonmissing Data

Like myself, this is ugly but it works.  The column generated by txnelson's script can have unwanted instances of the forward slash preceding the text.  I kill off the formula in the column and replace with a different column using regex to eliminate any preceding /.

 

	New Column("Tools2", Character, "Nominal", Formula(As Constant(
	dt = Current Data Table();
	start = 6;
	stop = N Cols( dt );
);
Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" );));

	Wait(1);
	Data Table( "Transpose of Tool_History" ):Tools2 << Delete Formula;

colList = Data Table( "Transpose of Tool_History" ) << Get Column Names( String );
 
For( i = 1, i <= N Cols( Data Table( "Transpose of Tool_History" ) ), i++,
	If( Contains( colList[i], "Row" ), /* select only columns with Row
	in the name */
		Column( colList[i] ) << Set Selected( 1 );
		
		
	)
);

Data Table( "Transpose of Tool_History" ) << Delete Columns();

	New Column("Tools", Character, "Nominal", Formula(Regex(:Tools2, "\w.*")));
	
	Wait(1);
	Data Table( "Transpose of Tool_History" ):Tools << Delete Formula;

Slán



SpannerHead
txnelson
Super User

Re: Concatenating Unique and Nonmissing Data

You are going to a lot of work to get your result.  Here is a simple modification to the last formula that I created that removes the preceding "/"

New Column( "Result",
	Character,
	"Nominal",
	set each value(
		As Constant(
			dt = Current Data Table();
			start = 5;
			stop = N Cols( dt );
		);
		Substr(
			Char( Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" ) ),
			2
		);
	)
);

Here is a complete example with a sample data table

Names Default To Here( 1 );

// Create a sample data table
New Table( "Sample",
	Add Rows( 3 ),
	Set Header Height( 46 ),
	New Column( "lot",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1324567, 1324567, 1324567] )
	),
	New Column( "step", Character, "Nominal", Set Values( {"Material", "Material", "Material"} ) ),
	New Column( "substep", Character, "Nominal", Set Values( {"Deposit", "CD Measure", "Measure"} ) ),
	New Column( "Label", Character, "Nominal", Set Values( {"machine", "machine", "machine"} ) ),
	New Column( "Row 1", Character, "Nominal", Set Values( {"Machine A", "Caliper 1", "Tapemeasure 1"} ) ),
	New Column( "Row 2", Character, "Nominal", Set Values( {"", "Caliper 2", ""} ) ),
	New Column( "Row 2 2", Character, "Nominal", Set Values( {"Machine A", "", ""} ) ),
	New Column( "Row 4", Character, "Nominal", Set Values( {"", "", "Tapemeasure 3"} ) ),
	New Column( "Row 5", Character, "Nominal", Set Values( {"Machine B", "", ""} ) ),
	New Column( "Row 6", Character, "Nominal", Set Values( {"", "", ""} ) ),
	New Column( "Row 7", Character, "Nominal", Set Values( {"", "", ""} ) )
);
	
// Add the concatenated column, with the "/" removed from begining	
New Column( "Result",
	Character,
	"Nominal",
	set each value(
		As Constant(
			dt = Current Data Table();
			start = 5;
			stop = N Cols( dt );
		);
		Substr(
			Char( Concat Items( Associative Array( dt[Row(), Index( start, stop )] ) << get keys, "/" ) ),
			2
		);
	)
);

Jim
SpannerHead
Level IV

Re: Concatenating Unique and Nonmissing Data

I knew it was ugly!  Much better indeed.

 

Appreciated as always.

 


Slán



SpannerHead