Add columns by refering to column number

[#1]

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.

[#2 · Accepted solution]

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

[#3]

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 ) 

       ) 

);