how to replace a numeric value in a variable

[#1]

Hi All,

this might be very easy so forgive me, i am trying to replace a numeric value in a variable with another value and am trying to do so with the following syntax

Acc << select where( :lo50%expGMT > 10.0 );
Acc << replace (5);

this deletes the whole row rather then replace any values under 10 with 5.

Can anyone help me?????

[#2]

OK....you actually had the answer.......that I had misinterpreted.  The formula is what is causing the issue.  You can not change the value in a cell within a formula.  The formula will always trump the change.  So what you need to do is to delete the formula as below, or to incorporate the change into the formula......

Here is the method to delete the formula

Names Default To Here( 1 );
Acc = New Table( "Example",
	Add Rows( 8 ),
	New Column( "base 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 4, 8, 16, 32, 64, 128] )
	),
	New Column( "base 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [12, 12, 12, 12, 12, 12, 12, 12] )
	),
	New Column( "base 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[10.684, 9.454, 8.4053, 7.4053, 6.4053, 5.3707, 4.3707, 2.4886]
		)
	),
	New Column( "base 4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1645, 701, 339, 170, 85, 41, 21, 6] )
	),
	New Column( "base 5",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1645, 823, 411, 206, 103, 51, 26, 13] )
	),
	New Column( "base 6",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [823, 412, 206, 103, 52, 26, 13, 7] ),
		Set Display Width( 53 )
	),
	New Column( "base 7",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [3290, 1646, 822, 412, 206, 102, 52, 26] )
	),
	New Column( "base 8",
		Character,
		"Nominal",
		Set Values(
			{"Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Fail"}
		)
	),
	New Column( "lo50%expGMT",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Ceiling( :base 5 / 2 ) ),
		Set Selected,
		Set Display Width( 104 )
	)
);
Acc << run formulas;

// Delete the formula and set values to real numbers
Acc:Name("lo50%expGMT" )<<delete formula;

Acc:Name("lo50%expGMT" )[Acc << get rows where( :Name("lo50%expGMT") < 10.0 )] = 5

And here is the data table with the formula changed to incorporate the required change.

Names Default To Here( 1 );
Acc = New Table( "Example",
	Add Rows( 8 ),
	New Column( "base 1",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 4, 8, 16, 32, 64, 128] )
	),
	New Column( "base 2",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [12, 12, 12, 12, 12, 12, 12, 12] )
	),
	New Column( "base 3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[10.684, 9.454, 8.4053, 7.4053, 6.4053, 5.3707, 4.3707, 2.4886]
		)
	),
	New Column( "base 4",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1645, 701, 339, 170, 85, 41, 21, 6] )
	),
	New Column( "base 5",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1645, 823, 411, 206, 103, 51, 26, 13] )
	),
	New Column( "base 6",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [823, 412, 206, 103, 52, 26, 13, 7] ),
		Set Display Width( 53 )
	),
	New Column( "base 7",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [3290, 1646, 822, 412, 206, 102, 52, 26] )
	),
	New Column( "base 8",
		Character,
		"Nominal",
		Set Values(
			{"Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Pass", "Fail"}
		)
	),
	New Column( "lo50%expGMT",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( If(Ceiling( :base 5 / 2 ) >= 10, Ceiling( :base 5 / 2 ), 5 ) ), 
		Set Selected,
		Set Display Width( 104 )
	)
);

[#3]

Sorry for my igorance Jim, but your saying i should incorporate it all when creating the variable so it should be:

Acc << New Column( "lo50%expGMT", Numeric, Continuous, Formula(round(:expGMT / 2 ))); this is the original creation of the variable code.

Change to :

Acc << New Column( "lo50%expGMT", Numeric, Continuous, Formula(If(round(:expGMT / 2 ) >= 10, (5))));

When l run this, l now get the variable lo50%expGMT will all "5"'s so it replaces all values and a big full stop for the last value, instead of 823,412,206,103,52,26,13 and 5

[#4 · Accepted solution]

You did not copy the formula correctly.  It is:

Acc << New Column( "lo50%expGMT", Numeric, Continuous, Formula(If(round(:expGMT / 2 ) >= 10,round(:expGMT / 2 ), 5)));

Please review the structure of an IF() clause in the Scripting Guide

     Help==>Books==>Scripting Guide

There are 3 sections to an IF() clause.

     If( comparison, what to do if the comparision is true, what to do if the comparison is not true);

Therefore:

If(     round(:expGMT / 2 ) >= 10 // The comparision
 
  ,round(:expGMT / 2 ) // What to do if true
 
    , 5  // what to do if not true
 
)));

[#5]

Yes your right, my apologies, thanks Jim, very much appreciated, has worked a treat

[#6]

when i just run this:

Acc << select where( :lo50%expGMT < 10);
Acc << replace (5);

i can see it has selected the row, instead of inserting 5 as a replecement for values under 10, it deletes the whole row.

Any suggestions????