topic Re: if "compare" column value>100%, then change the value to 100%, if <=100%, then keep the value no change in Discussions

if "compare" column value is greater than 100%, then change the value to 100%, if less than or equal to 100%, then keep the value no change

Theresa — Fri, 09 Jun 2023 23:27:30 GMT

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

New Column( "Compare", Numeric, "Continuous", Format( "Percent", 12, 0 ), Formula( :weight / :height ) );

//change the value on position of :weight[2] to 59. But seems not work. could you help edit the script on this place?


dt:weight[2] << Set Values( 58 );


// I hope realize if the column "compare" result >100%, then output 100%, if <100% then keep the value no change. then how to edit script below? If( :Compare>100%,100%, no change);

Re: if "compare" column value>100%, then change the value to 100%, if <=100%, then keep the value no change

txnelson — Fri, 29 May 2020 08:52:31 GMT

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "Compare",
	Numeric,
	"Continuous",
	Format( "Percent", 12, 0 ),
	Formula( :weight / :height )
);

// Changing the 2nd rows value for Weight to 59 will work with
// a simple assignment statement
dt:weight[2] = 59;

// One can not change a cell in a formula column,
// unless you change the complete formula

dt:compare << set formula(
		:weight / :height;
		if( :weight / :height > 1, 1)
);

Probably the method that I use most often, is to Delete the formula from a column that was created using a formula. Deleting the formula, converts the column into a normal static column, keeping all of the values. You can then just make the changes you need, using standard assignment statements.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
New Column( "ratio",
	Numeric,
	"Continuous",
	Format( "Percent", 12, 0 ),
	Formula( :weight / :height )
);
dt << run formulas;

dt:ratio << delete formula;

For Each Row( If( :ratio > 1, :ratio = 1 ) )
;

Re: if "compare" column value>100%, then change the value to 100%, if <=100%, then keep the value no change

Theresa — Wed, 10 Jun 2020 07:32:18 GMT

for the first method: if( :weight / :height > 1, 1); ------This is update all the values which exceed 100% successful. But change the number less than 100% to "." which should keep no change. don't know how to improve this script.

For the second method:For Each Row( If( :ratio > 1, :ratio = 1 ) ); works well. thank you so much.