How to delete the column & row and choose specific row name as a new column?

mystylelife19

Hello, my name is Jamal. I'm from Malaysia, and i really don't have knowledge about scripting JSL. So my project is, every table that i open from data Wafer, it comes with unwanted row & column. So my objective is, the data will only work if the name row Parameter and other be a new column. Also i must delete some specific row that i did not use. So my question to someone expert scripting. Can you write down, the program that if i choose any data table it automatically delete the other row inside red marker and make a row Parameter as a new column? 

 

P/s not every data table have a same row parameter (44)

tempsnip.png

txnelson

Here is the way that I would approach the issue.  Open the csv file in JMP and then run this below script.  It will load the global data into table variables, change the column names, and then convert the test columns into numeric etc.

Names Default To Here( 1 );
dt = Current Data Table();

// Add Global info as table variables
theRow = 1;
While( Column( dt, 1 )[theRow] != "Parameter",
	If( Column( dt, 2 )[theRow] != "",
		dt << new table variable( Column( dt, 1 )[theRow],
			Column( dt, 2 )[theRow]
		)
	);
	theRow++;
);

// Get rid of unwanted rows
dt << select where( As Column( dt, 3 ) == "" & As Column( dt, 4 ) == "" );
dt << delete rows;

// Rename columns
theRow = (dt << get rows where( As Column( dt, 1 ) == "Parameter" ))[1];
For( i = 1, i <= N Cols( dt ), i++,
	If( Column( dt, i )[theRow] != "",
		Column( dt, i ) << set name( Column( dt, i )[theRow] )
	)
);
dt << delete rows(theRow);

// Attempt to set meta data for columns
// List of know columns
metaList = {
	"SBIN", "numeric", "ordinal",
	"HBIN", "numeric", "ordinal",
	"DIE_X", "numeric", "ordinal",
	"DIE_Y", "numeric", "ordinal",
	"SITE", "numeric", "ordinal",
	"TOTAL_TESTS", "numeric", "continuous",
	"X", "numeric", "ordinal",
	"Y", "numeric", "ordinal",
	"SITE_NO", "numeric", "ordinal",
	"PassFail", "numeric", "ordinal",
	"TimeStamp", "numeric", "timestamp",
	"PartSN", "character", "nominal"	
};
// Process metaList into separate lists for easier processing
colList = {};
typeList = {};
modelList = {};
For( i = 1, i <= N Items( metaList ), i++,
	If(
		Mod( i, 3 ) == 1, Insert Into( colList, metaList[i] ),
		Mod( i, 3 ) == 2, Insert Into( typeList, metaList[i] ),
		Insert Into( modelList, metaList[i] )
	)
);
For( i = 1, i <= N Cols( dt ), i++,
	foundName = Contains( colList, Column( dt, i ) << get name );
	If(
		foundName,
			If(
				Contains( {"ordinal", "nominal", "continuous"}, modelList[foundName] ),
					Column( dt, i ) << dataType( typeList[foundName] ) << modelingtype( modelList[foundName] ),
				modelList[foundName] == "timestamp",
					For( k = 1, k <= N Rows( dt ), k++,
						Column( dt, i )[k] = Word( 1, Char( Column( dt, i )[k] ), "_ " ) || "/" || Word( 2, Char( Column( dt, i )[k] ), "_ " ) || "/"
						 || Word( 3, Char( Column( dt, i )[k] ), "_ " ) || " " || Word( 2, Char( Column( dt, i )[k] ), " " )
					);
					Column( dt, i ) << datatype( numeric ) << modelingtype( continuous ) << set informat( "y/m/d h:m:s" ) <<
					Format( "y/m/d h:m:s", 18, 0 );,

			),
		Is Missing( Num( Column( dt, i )[1] ) ) == 0,
			Column( dt, i ) << datatype( numeric ) << modelingtype( continuous );
		
	);
);