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

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

mystylelife19 — Sat, 10 Jun 2023 23:47:19 GMT

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)

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

mystylelife19 — Tue, 19 Apr 2022 08:10:03 GMT

Sorry forgot to put the data table from Wafer

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

ErraticAttack — Tue, 19 Apr 2022 09:55:55 GMT

This might do the trick:

Names Default to Here( 1 );
dt = Current Data Table();
loc = Contains( Column( dt, 1 ) << Get Values, "Parameter" );
dt << Select Rows( 1::loc - 1 ) << Delete Rows;
loc = Contains( Column( dt, 1 ) << Get Values, "PID-1" );
dt << Select Rows( 2::loc - 1 ) << Delete Rows;

For( i = 1, i <= N Col( dt ), i++,
	Column( dt, i ) << Set Name( Column( dt, i )[1] )
);

dt << Select Rows( [1] ) << Delete Rows

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

txnelson — Tue, 19 Apr 2022 12:16:17 GMT

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

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

mystylelife19 — Tue, 19 Apr 2022 18:41:29 GMT

Oh my god. Thankyou so much sir.

Can i ask for little help?

For the result of data table must be in Bivariate not in Mosaic. I already copy & paste your coding before my coding action Distribution. But it appears in Mosaic result. So can you refer to my coding and fix it?

Please refer to the picture i just upload

- If i run the scripting it appears the site no 1 & site no 2 separate each other. how to combine the site no 1 & site no 2 like in 1 windows?

User_Input = New Window( "JMP AUTOMATION", << modal(),
	hlistbox(
		Text Box( "Enter Wafer ID:" ),
		ID_Wafer = Text Edit Box( "", <<set width( 200 ) ),
	),
	Button Box( "OK", 
		ID_Wafer = ID_Wafer << get text();
	),
);

//this below should put your coding sir



Distribution(
	Continuous Distribution(
		Column( :IL_ALL_MAG_C1_S21 ),
		Normal Quantile Plot( 1 )
	),
	SendToReport(
		Dispatch(
			{"IL_ALL_MAG_C1_S21"},
			"",
			Picture Box( 5 ),
			{Set Summary Behavior( "Collapse" )}
		),
		Dispatch(
			{"IL_ALL_MAG_C1_S21"},
			"Distrib Quantile Plot",
			FrameBox,
			{Row Legend(
				IL_ALL_MAG_C1_S21,
				Color( 1 ),
				Color Theme( "Blue to Gray to Red" ),
				Marker( 0 ),
				Marker Theme( "" ),
				Continuous Scale( 0 ),
				Reverse Scale( 0 ),
				Excluded Rows( 0 )
			)}
		)
	)
);

Bivariate( Y( :Y ), X( :X ), Where( :WAFER_ID == "ID_Wafer" & :SITE_NO == 1 ) );

Bivariate( Y( :Y ), X( :X ), Where( :WAFER_ID == "ID_Wafer" & :SITE_NO == 2 ) );
//for ID_Wafer how to put the data that we type on Text Edit Box and shows as WAFER_ID?

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

txnelson — Tue, 19 Apr 2022 19:29:38 GMT

Change your Bivariate JSL to

Bivariate( Y( :Y ), X( :X ), Where( :WAFER_ID == ID_Wafer & :SITE_NO == 1 ) );

Bivariate( Y( :Y ), X( :X ), Where( :WAFER_ID == ID_Wafer & :SITE_NO == 2 ) );

Also,

Please don't jusr cut/paste and use the JSL I provided. Please go through it and learn it.

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

mystylelife19 — Wed, 20 Apr 2022 00:56:37 GMT

Okay sir, many thanks to you.

i try okey :))