topic Concatenating Unique and Nonmissing Data in Discussions

Concatenating Unique and Nonmissing Data

SpannerHead — Mon, 16 Dec 2024 22:09:59 GMT

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.

Re: Concatenating Unique and Nonmissing Data

txnelson — Mon, 16 Dec 2024 23:00:48 GMT

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

Re: Concatenating Unique and Nonmissing Data

SpannerHead — Tue, 17 Dec 2024 14:29:12 GMT

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.

Re: Concatenating Unique and Nonmissing Data

txnelson — Tue, 17 Dec 2024 16:36:25 GMT

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

Re: Concatenating Unique and Nonmissing Data

SpannerHead — Wed, 18 Dec 2024 14:30:12 GMT

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;

Re: Concatenating Unique and Nonmissing Data

txnelson — Wed, 18 Dec 2024 15:27:51 GMT

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

Re: Concatenating Unique and Nonmissing Data

SpannerHead — Wed, 18 Dec 2024 21:00:28 GMT

I knew it was ugly! Much better indeed.

Appreciated as always.