topic Re: Trying to create a column and fill it with a formula but I get an error. in Discussions

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

CrownofTears — Fri, 11 Aug 2023 01:41:35 GMT

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

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

ErraticAttack — Fri, 11 Aug 2023 05:42:59 GMT

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

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

mmarchandTSI — Fri, 11 Aug 2023 13:25:20 GMT

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

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

hogi — Fri, 11 Aug 2023 16:11:28 GMT

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

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

jthi — Fri, 11 Aug 2023 16:34:42 GMT

Good post regarding this topic is . 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+)

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

hogi — Fri, 11 Aug 2023 19:36:47 GMT

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

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

CrownofTears — Fri, 11 Aug 2023 19:12:53 GMT

Not working :(

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

CrownofTears — Fri, 11 Aug 2023 19:21:35 GMT

Just As Column() instead of column worked!

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

CrownofTears — Fri, 11 Aug 2023 19:21:46 GMT

Just As Column() instead of column worked!

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

hogi — Fri, 11 Aug 2023 19:28:50 GMT

Seems to work "once" (the values are OK) - but (at least for my test case) variant #1 reports an error with the formula when you open it afterwards:

Variant # 6 seems to solve this issue:

but it will definitely fail once Jmp is closed and forgets what dt means ....

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

mmarchandTSI — Fri, 11 Aug 2023 19:40:24 GMT

@CrownofTears, Based on your original JSL:

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( (As Column( Expr( Column Name( j ) ) ) - As Column( Expr( Column Name( j + 1 ) ) )) )
			)
		)
	);
);

I like the clean As Column("column name") functions, and it's not ugly like my Eval( Parse(...) ) solution.

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

ErraticAttack — Fri, 11 Aug 2023 20:06:58 GMT

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

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

ErraticAttack — Fri, 11 Aug 2023 20:07:45 GMT

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 )[]

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

hogi — Fri, 11 Aug 2023 20:40:28 GMT

Cool trick :)

Column(1)[]

instead of

Column(1)[row()]