667407_en-US

liliconcom

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

View more...

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

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

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.

Just As Column() instead of column worked!

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

Just As Column() instead of column worked!

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

Not working

 

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

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.

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

 

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

@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.

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.

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.

@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.

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.

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.

Cool trick

 

Column(1)[] 

instead of 

Column(1)[row()]