cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
euge
Level III

How to create column names based off a list?

I have a list comprised of stringlist={Albert,Bert,Cathy ,Daniel,Total} that are column names in a table

I'd like to create in the same table 4 new columns, with new column names Albert Total,Bert Total,Cathy Total, Daniel Total where the formula of Albert total = Albert/Total, Bert = Bert/Total, etc

The column names albert, bert, cathy, daniel can be variable, but the column name total is always the same.

Here's what I tried:

for(i=1,i<=nitems(stringlist),i++,

     dt<<new column name(eval(stringlist)||" Total",

          formula((eval(parse(stringlist))/:total)

          )

     )

);

I've tried variants of evallist, evalexpr, parse with no avail...

2 REPLIES 2
pmroz
Super User

Re: How to create column names based off a list?

This will do the trick:

dt = New Table("Untitled", Add Rows( 5 ),

   New Column( "Albert", Numeric,

        Continuous,    Format( "Best", 12 ), Set Values( [1, 2, 3, 4, 5] )    ),

    New Column( "Bert", Numeric, Continuous, Format( "Best", 12 ),

        Set Values( [6, 7, 8, 9, 10] )),

    New Column( "Cathy", Numeric, Continuous, Format( "Best", 12 ),

        Set Values( [11, 12, 13, 14, 15] )),

    New Column( "Daniel", Numeric, Continuous, Format( "Best", 12 ),

        Set Values( [16, 17, 18, 19, 20] )),

    New Column( "Total", Numeric, Continuous, Format( "Best", 12 ),

        Set Values( [23, 43, 222, 32, 444] )),

);

stringlist = dt << get column names (string);

For( i = 1, i <= N Items( stringlist ), i++,

    one_col = stringlist[i];

    if (one_col != "Total",

        new_col = one_col || " Total";

        col_string = "dt << new column(new_col, formula(:" || one_col || " / :Total))";

        eval(parse(col_string));

    );

);

euge
Level III

Re: How to create column names based off a list?

Cool!  I was trolling thru the solutions, and was using the below solution (in case anyone else is curious) - yours is better because it doesn't rely on column position...

nlist=ncols(dt)

cols=dt<<get column names;

totals=column("total")<<get as matrix;

for(i=1,i<=nlist-1,i++,
  newcolexpr=expr(

  dt<< new column( char(cols)||"_Total",numeric,formula(expr(cols))/expr(totals))));

  Eval(eval expr(newcolexpr));

)

);