Choose Language Hide Translation Bar
Highlighted
Theresa
Level III

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
Highlighted
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
Highlighted
Theresa
Level III

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.
Article Labels