486881_en-US

liliconcom

creating formula using existing column names

Names Default To Here( 1 );
dt = Current Data Table();
numcol=dt << Get Column Names(Numeric);
charcol=dt << Get Column Names( Character);


For( k = 1, k <= N Items( numcol ), k++,
colname=column(numcol[k]) <<GET name=""></GET>

Hi JMP users,

I have a data table that I need to do some conversions on numeric columns based on predefined criteria. 

I'm getting error "Expression does not fit available column types at row 1 in access or evaluation of 'Bad Argument' ".

Any idea on how to resolve this? Appreciated!

ScriptingRe: creating formula using existing column names

You have 2 issues going on here.

1. When a formula is presented to a new column, it is not processed before being added as the formula.  Therefore, when you are specifying

   :Column(dt, colname)

   JMP does not change it to the first numeric column name, then the second, etc.  The JSL author must use code that will expand the reference into the complete/ finished JSL statement before passing it to the Format element
2. The column names in your data table, i.e. 1, 2, 3, 4 etc. will cause problems in JMP interpreting them, unless you specifically indicate they are to be interpreted as column names.  Therefore, you need to use the :"column name"n structure

Below is a reworking of your code that produces the new columns you want

Names Default To Here( 1 );
dt = Current Data Table();
numcol = dt << Get Column Names( Numeric );
charcol = dt << Get Column Names( Character );


For( k = 1, k <= N Items( numcol ), k++,
 colname = Column( numcol[k] ) << Get Name;

 Eval(
  Substitute(
    Expr(
     dt << New Column( "site" || numcol[k],
      formula(
       If(
        :ID == "item5", (__colname__ * 0.5) / 20,
        :ID == "item8", (__colname__ * 0.8) / 20,
        __colname__ 
       )
      )
     )
    ),
   Expr( __colname__ ), Parse( ":\!"" || colname || "\!"n")
  )
 );
);

Re: creating formula using existing column names

Thanks Jim it works!