topic Re: call a column by referring to variable in Discussions

call a column by referring to variable

YBerger — Sat, 10 Jun 2023 23:14:38 GMT

A complete newbie here,

could not find the solution in older posts,

I have created a variable containing an existing column name. I want to create a new column, were the data from two other columns is summed (calling the columns by variables). the usage of variable is for later use in a for loop.

Thanks for helping!

for the below code:

dt = Current Data Table();

columnname1 = Column( dt, 1 ) << Get Name;
columnname2 = Column( dt, 2 ) << Get Name;

dt << New Column( "new_" || columnname1, Numeric , continuous, formula(column(dt, columnname1)+column(dt, columnname2)));

I get the following error:

Column new_Column 1 2 Formula Interrupted
Cannot convert argument to a number [or matrix] 1 times At rows: 2 Operation: Add, Column( dt, columnname1 ) + /*###*/Column( dt, columnname2 ) /*###*/
Formula evaluation errors have been ignored

Column( "new_Column 1 2" )

Re: call a column by referring to variable

David_Burnham — Fri, 12 Jun 2020 09:29:16 GMT

You've already created variables that reference the columns. You can use those in your formula, but you need to use the AsColumn function:

formula( AsColumn(columnname1) + AsColumn(columnname2) )

Re: call a column by referring to variable

txnelson — Fri, 12 Jun 2020 09:41:58 GMT

You can get it to work, with

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnname1 = Column( dt, 4 ) << Get Name;
columnname2 = Column( dt, 5 ) << Get Name;

dt << New Column( "new_" || columnname1,
	Numeric,
	continuous,
	formula(
		As Column( dt, columnname1 ) + As Column( dt, columnname2 )
	)
);

However, there is a potential problem with this. The stored formula is:

This formula will continue to work, as long as your JMP session

Has the variables, dt, columnname1 and columnname2 defined. What happens if you save the data table, shutdown JMP, and then tomorrow you come in and startup JMP, and open the data table. The formula will not find those variables, and the formula will fail.
Within the session, the variables, dt, columnname1 and columnname2, do not change their value. So, if you create this column, and later on in your script, you happen to change the value of variable columnname1, since formula columns in a JMP data table are dynamic, the formula will rerun when changes to the data table take place, JMP will use the new value of the variable, and will create new values for the formula column.

What you really need to do, is to use a method that creates the actual JSL syntax for the formula. In the above example, what you really want is for the formula to be

:height + :weight

The JSL below is one way to do this. It uses the Substitute() function to implant the actual column names into the formula.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
columnname1 = Column( dt, 4 ) << Get Name;
columnname2 = Column( dt, 5 ) << Get Name;

Eval(
	Substitute(
			Expr(
				dt << New Column( "new_" || columnname1,
					Numeric,
					continuous,
					formula( __c1__ + __c2__ )
				)
			),
		Expr( __c1__ ), Parse( ":" || columnname1 ),
		Expr( __c2__ ), Parse( ":" || columnname2 )
	)
);

Re: call a column by referring to variable

YBerger — Fri, 12 Jun 2020 12:30:24 GMT

thank you for your quick reply. it works!

Re: call a column by referring to variable

David_Burnham — Fri, 12 Jun 2020 13:33:58 GMT

@txnelson is quite right about this. The method I suggested was a lazy option and in hindsight not an approach I would recommend. If you just want a column with correct values you can do a calculation in a loop and not bother with a formula; if the formula is important to you then you need to check that your script has correctly encoded the formula - it's not sufficient for the numbers to appear correct. Script variables will not persist beyond the lifetime of the script, so the formula needs to correctly include column references - this is achieved by using expression-substitution techniques such as the one illustrated by Jim.

Re: call a column by referring to variable

NisKorsgaard — Fri, 21 Jun 2024 08:57:07 GMT

Just for future readers of this question:

I used the above code to pass string column names to formulas but had problems with the use of

Parse(":" || column_name)

Parse is vulnerable to odd column names since column names like "a - b" would be interpreted as column "a" minus variable "b".

Instead use

As Name(columnname)

which fixes this issue.