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.
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, "/" );
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.
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
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;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
);
)
);
I knew it was ugly! Much better indeed.
Appreciated as always.
