topic Re: how to replace a numeric value in a variable in Discussions

how to replace a numeric value in a variable

Mickyboy — Mon, 18 Jun 2018 13:28:27 GMT

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?????

Re: how to replace a numeric value in a variable

txnelson — Mon, 18 Jun 2018 11:19:13 GMT

Assuming that "Acc" is the pointer to the data table, and that "Acc:lo50%expGMT" is the name of the column that you both want to compare with and then change it"s value, the following code will change the values

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

I choose to use the :Name() function in the code, since the column name contains a special character "%" that JMP may get confused over.

Re: how to replace a numeric value in a variable

Mickyboy — Mon, 18 Jun 2018 11:58:52 GMT

Thanks Jim, l really appreciate it, will try it at work tomorrow

Re: how to replace a numeric value in a variable

Mickyboy — Mon, 18 Jun 2018 23:40:55 GMT

Hi Jim,

My apologies, l am new to JMP so struggling a little bit with the Syntax, this didnt work either, Acc is the pointer to the data table "Accuracy" and the name of the variable is lo50%expGMT.

Re: how to replace a numeric value in a variable

txnelson — Tue, 19 Jun 2018 00:06:13 GMT

Oops...I had an error...try this

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

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 00:07:41 GMT

l think the problem is that the variable lo50%expGMT has a formula and is derivied by dividing another variable by 2, would this matter??

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 00:19:21 GMT

Sorry Jim, still didnt work

Re: how to replace a numeric value in a variable

txnelson — Tue, 19 Jun 2018 00:22:59 GMT

The fact that lo50%expGMT is a column created with a formula will not make any differenct

What messages are you seeing in the log?

If you run just the following JSL, you will see the results in the log. It should just give you a matrix with the row numbers for all of the rows where lo50%expGMT is greater than 10

Acc << get rows where( :Name("lo50%expGMT") > 10.0 );

Can you attach a sample data table?

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 12:47:31 GMT

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????

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 01:04:40 GMT

1 12 10.684 1645 1645 823 3290 Pass
2 12 9.4540 701 823 412 1646 Pass
4 12 8.4053 339 411 206 822 Pass
8 12 7.4053 170 206 103 412 Pass
16 12 6.4053 85 103 52 206 Pass
32 12 5.3707 41 51 26 102 Pass
64 12 4.3707 21 26 13 52 Pass
128 12 2.4886 6 13 7 26 Fail

it hasnt selected the row in question, the one in question is the last row, the variable in question is lo50%expGMT, which the forth from the left.

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 01:07:03 GMT

Sorry that should be the 6th from the left

Re: how to replace a numeric value in a variable

txnelson — Tue, 19 Jun 2018 02:38:42 GMT

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

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 12:48:33 GMT

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

Re: how to replace a numeric value in a variable

txnelson — Tue, 19 Jun 2018 12:50:35 GMT

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

Re: how to replace a numeric value in a variable

Mickyboy — Tue, 19 Jun 2018 05:06:30 GMT

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