Trying to create a column and fill it with a formula but I get an error.

[#1]

View more...

Hi, I can make the script to fill the new column created.

This is the error I get:
 Has anyone an idea of whats wrong here?


Formula Interrupted
Cannot convert argument to a number [or matrix] at row 1 in access or evaluation of 'Subtract' , Column( j ) - /*###*/Column( j + 1 ) /*###*/
Formula evaluation errors have been ignored

For (j=3, j<=10, j=j+2,
	
	ColLen=Length(Char(Column name (j)));
	ColName=Left (Char(Column Name(j)),ColLen -5);
	NewCol=Substr(ColName,6);
	
	New Column(
		"Delta: "||NewCol,
		Numeric,	
		Formula (
			(Column (j)- Column(j+1))
		)
	)
);

[#2]

JSL uses dynamic scope -- I believe that you are working under the implicit assumption of lexical scope (which most programming languages use).

Because JSL uses dynamic scope, the scope-resolution for the name j is done at runtime, and when the formula is computing the name j is no longer in scope, thus it errors out.

To get around this, you can pre-evaluate parts of the code so that JMP doesn't have a variable to lookup within the column formula:

For( j = 3, j <= 10, j = j + 2, 
	
	ColLen = Length( Char( Column Name( j ) ) );
	ColName = Left( Char( Column Name( j ) ), ColLen - 5 );
	NewCol = Substr( ColName, 6 );
	
	Eval( Eval Expr(
	New Column( "Delta: " || NewCol, Numeric, Formula( (Column( Expr( j ) ) - Column( Expr( j + 1 ) )) ) );
	) )
);

[#3]

Not working

[#4]

I should have pointed out that to go from column reference to row-value, simply put in a [].  The following should work:

For( j = 3, j <= 10, j = j + 2, 
	
	ColLen = Length( Char( Column Name( j ) ) );
	ColName = Left( Char( Column Name( j ) ), ColLen - 5 );
	NewCol = Substr( ColName, 6 );
	
	Eval( Eval Expr(
	New Column( "Delta: " || NewCol, Numeric, Formula( (Column( Expr( j ) )[] - Column( Expr( j + 1 ) )[]) ) );
	) )
);

[#5 · Accepted solution]

It's because Column() is a reference to the column itself and not the values in the column.  I know how to do what you want in an ugly way.  The following script will work in the meantime until someone posts a prettier solution.

Names Default To Here( 1 );
For( j = 3, j <= 10, j = j + 2, 
	
	ColLen = Length( Char( Column Name( j ) ) );
	ColName = Left( Char( Column Name( j ) ), ColLen - 5 );
	NewCol = Substr( ColName, 6 );
	
	Eval(
		Parse(
			"New Column( \!"Delta: " || NewCol || "\!",
				Numeric,
				Formula(
				
					:\!"" ||
			(Column( j ) << Get Name) || "\!"n - :\!"" || (Column( j + 1 ) << Get Name) || "\!"n
					))"
		)
	);
);

[#6 · Accepted solution]

@mmarchandTSI , I agree,

Formula(Column( ...

won't work. 

How about using Substitute:

For( j = 3, j <= 10, j = j + 2, 
	
	ColLen = Length( Char( Column Name( j ) ) );
	ColName = Left( Char( Column Name( j ) ), ColLen - 5 );
	NewCol = Substr( ColName, 6 );
	
	Eval(Substitute(Expr(
	New Column( "Delta: " || NewCol, Numeric, Formula( (__colA__ - __colB__) ) );
	),Expr(__colA__),Name Expr(As Column(j)),Expr(__colB__),Name Expr(As Column(j+1)) ))
);

[#7]

I should have pointed out -- to go from a column reference to a row-value within a formula, put in a [] after the column reference

Column( 1)[] + Column( 2 )[]

[#8]

Cool trick

Column(1)[] 

instead of 

Column(1)[row()] 

[#9 · Accepted solution]

Good post regarding this topic is Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute . In your case you do need a bit more complicated solution as you are using column indices instead of column names if you want to have formulas (most of the time I would suggest using names instead of indices). Most of the time I use Eval(EvalInsert()) but substitute is also good option

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("Column 1", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3])),
	New Column("Column 2", Numeric, "Continuous", Format("Best", 12), Set Values([2, 3, 4])),
	New Column("Column 3", Numeric, "Continuous", Format("Best", 12), Set Values([3, 4, 5])),
	New Column("Column 4", Numeric, "Continuous", Format("Best", 12), Set Values([4, 5, 6])),
	New Column("Column 5", Numeric, "Continuous", Format("Best", 12), Set Values([6, 7, 8]))
);

For(j = 1, j < 5, j = j + 2,
	// ColLen = Length(Char(Column Name(j)));
	// ColName = Left(Char(Column Name(j)), ColLen - 6);
	// NewCol = Substr(ColName, 6);
	new_col = Word(2, Column(dt, j) << get name, " ");
	
	Eval(Eval Expr(
		New Column("Delta: " || new_col, Numeric, Formula(
			Expr(NameExpr(AsColumn(j))) - Expr(NameExpr(AsColumn(j + 1)))
		))
	));
);

Some additional functions/messages that might be of interest to you: << Get Column Names, Word() (search also for Words(), Item() and Items() from scripting index), For Each() (if you have JMP16+)

[#10 · Accepted solution]

After fiddling around a bit
- esp: wondering why #1 doesn't give the expected result:

dt = Open( "$Sample_data/iris.jmp" );
cols = {Sepal length,Sepal width, Petal length};

i = 2; 
Eval( Eval Expr( dt << New Column( "new1", Formula( As Column( Expr( i ) ) ) ) ) ); //works but the formula is corrupted
Eval( Eval Expr( dt << New Column( "new2", Formula( As Column( Expr( Column(i) ) ) ) ) ) );
Eval( Eval Expr( dt << New Column( "new3", Formula( As Column( Expr( Char( cols[i] ) ) ) ))) );
Eval(Substitute(Expr(dt << New Column( "new4", Formula( __col__ ) )),Expr(__col__),Name Expr(As Column(i)) ));
Eval(Eval Expr(dt << New Column("new5", Formula(Expr(NameExpr(AsColumn(i)))))));

// new:
Eval( Eval Expr( dt << New Column( "new6", Formula( As Column( dt, Expr( i ) ) ) ) ) ); 

... I found a new variant which doesn't need the enclosing Name Expr to make it work *)
Interesting ...

*) work - till Jmp is closed and started again