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" )
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) )
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) )
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
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 )
)
);
thank you for your quick reply. it works!
@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.