cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
luqi
Level II

Add columns by refering to column number

Hello,

I am trying to add columns together by refereing to the columns numbers instead of their names. Here is an example of what I worte so far

dt = Current Data Table();

dt << New Column("Luq", Numeric, continuos, Format ("Best", 5), << Set Each Value (2));

dt << New Column("Fin", Numeric, continuos, Format ("Best", 5), << Set Each Value (3));

dt << New Column("Final Result", Numeric, continous, Format("Best", 5 ), Formula ( Add Columns ({6,7})), << Add Column properties(Set property ("Value Colors", {if (:Final Result >= 9 = 3,  :Final Result >= 5 = 9, :Final Result <5 = 4 )}), Color Cell by Value));

You can also see that I am trying to color the columns by their value number. What I want to code is "if that the value is greater than 9 color RED, if it is greater that 5 color YELLOW and else color GREEN"

Thank You.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Add columns by refering to column number

Hi luqi,

The "As Column()" function will help you with adding columns by position in the data table. To add the values in the 6th and 7th columns:

dt << New Column( "Final Result",

  Numeric,

  continuous,

  Format( "Best", 5 ),

  Formula( As Column( column(6) ) + As Column( column(7) ) )

);

As for the cell coloring you desire, it's easier to accomplish by coloring the cells directly, without a column property:

For( i = 1, i <= N Row( dt ), i++,

  If(

  Column( dt, "Final Result" ) >= 9, Column( dt, "Final Result" ) << Color Cells( red, {i} ),

  Column( dt, "Final Result" ) >= 5, Column( dt, "Final Result" ) << Color Cells( yellow, {i} ),

  Column( dt, "Final Result" ) << Color Cells( green, {i} )

  )

);

Cheers,

Jordan

View solution in original post

2 REPLIES 2

Re: Add columns by refering to column number

Hi luqi,

The "As Column()" function will help you with adding columns by position in the data table. To add the values in the 6th and 7th columns:

dt << New Column( "Final Result",

  Numeric,

  continuous,

  Format( "Best", 5 ),

  Formula( As Column( column(6) ) + As Column( column(7) ) )

);

As for the cell coloring you desire, it's easier to accomplish by coloring the cells directly, without a column property:

For( i = 1, i <= N Row( dt ), i++,

  If(

  Column( dt, "Final Result" ) >= 9, Column( dt, "Final Result" ) << Color Cells( red, {i} ),

  Column( dt, "Final Result" ) >= 5, Column( dt, "Final Result" ) << Color Cells( yellow, {i} ),

  Column( dt, "Final Result" ) << Color Cells( green, {i} )

  )

);

Cheers,

Jordan

txnelson
Super User

Re: Add columns by refering to column number

Luqi,

Jordan's example does work well, but it does have one drawback.  The formula created contains the column numbers, not the column names the column numbers refer to.  Therefore, you are left with an issue that if you change the structure of the data table, and either move or delete any column before the columns specified in the formula, the formula will change, and base its values on the values of the new columns that are in the number position(s) specified in the formula.

This can be mitigated in a couple of ways.

1.  If you delete the formula after you create the new column, it will break the link to the columns specified by number, and change the row values to the static values the formula had determined.

    column(6a0<<delete property("formula");

2.  You can use a slightly more complex structure, and force the actual column name into the formula

 

Eval(

       Substitute(

                       Expr( 

                 dt << New Column( "test2", formula( _a_ + _b_ ) ) 

                ), 

              Expr( _a_ ), Parse( Column( 6 ) << get name ),

              Expr( _b_ ), Parse( Column( 7 ) << get name ) 

       ) 

);

 

Jim