Find and replace data which contain certain value range in specific column in JSL

LipYeong — Thu, 08 Jun 2023 16:29:10 GMT

Hi! all,

I would like to find and replace data which contain certain value range in specific column by using jmp script

However, the code didn't take effect

Here is my code that want to loop through the data in "mark" column and look for value that more than 1000 and substitute with 99

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

headerList = dt << get column names( string );

For( i = 1, i <= N Items( headerList ), i++,

	If(  Contains(headerList[i], "mark"), 
	dt << Begin Data Update;
	For Each Row( dt, As Column( dt, headerList[i]) = Substitute( As Column( dt, headerList[i]) > 1000 , 99 ));
	dt << End Data Update;
	);	
	
);

Does anyone knows what's went wrong with the code?

Appreciate the helps. Thank you

Re: Find and replace data which contain certain value range in specific column in JSL

txnelson — Tue, 14 Mar 2023 17:57:48 GMT

I am assuming that you want all values above 1000 converted to 99, if so, the below script will do this more efficiently than using a For Each Row()

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

headerList = dt << get column names( string );

For( i = 1, i <= N Items( headerList ), i++,
	If( Contains( headerList[i], "mark" ),
		theRows = dt << get rows where( As Column( headerList[i] ) > 1000 );
		If( Length( theRows ) > 0,
			Column( headerList[i] )[theRows] = 99
		);
	);	
);

topic Re: Find and replace data which contain certain value range in specific column in JSL in Discussions

Find and replace data which contain certain value range in specific column in JSL

Re: Find and replace data which contain certain value range in specific column in JSL