nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Concatenating Unique and Nonmissing Data

SpannerHead

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.

Accepted answers

txnelson

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

txnelson

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
		);
	)
);

All comments

txnelson

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, "/" );

SpannerHead

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.

txnelson

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

SpannerHead

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;