turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Add columns by refering to column number

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 7, 2016 9:12 AM
(1820 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 11, 2016 5:03 PM
(1980 views)

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

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 11, 2016 5:03 PM
(1981 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 11, 2016 8:38 PM
(1632 views)

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