cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Theresa
Level IV

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

 

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

 

2 REPLIES 2
txnelson
Super User

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

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 ) )
;
Jim
Theresa
Level IV

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


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.