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
- :
- Column difference- JSL syntax

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

Mar 29, 2017 12:55 PM
(1266 views)

Hi All,

How to access the contents of a column by the column reference? Since my column names keep changing with every loop in the script, I can't just simple use the column name to subtract.

dt is my data table and has 30 columns. I want to create a new column with dt[28]- dt[30].

Can anyone help me with syntax?

I really appreciate it. 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

Mar 30, 2017 2:36 AM
(2409 views)

Solution

This example shows how variables can be used for defining columns dynamically when 1) using the matrix notation for tables in JMP 13 or 2) setting a formula.

```
dt = New Table("Table Subscripting",
New Column("a", Set Values([1 2 3 4 5])),
New Column("b", Set Values([44 22 77 55 99])),
New Column("c", Set Values([59, 58, 57, 56, 55]))
);
startCol = 1;
endCol = N Col(dt);
// New column with the diff between endCol and startCol can be filled
// 1) with static values using matrix notation (JMP 13),
dt << New Column("Delta");
dt[0, endCol + 1] = dt[0, endCol] - dt[0, startCol];
// 2) or by a Column Formula
// (expression must partly be pre-evaluated if variables are used)
F = Eval Expr(Expr(Column(endCol))[] - Expr(Column(startCol))[]);
dt << New Column("Delta2", Formula(Name Expr(F)));
```

5 REPLIES

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

Mar 29, 2017 1:10 PM
(1259 views)

There is more than one way to refer to a column:

- :name
- Column( "name" )
- Column( data table reference, "name" )
- Column( index )
- Column( data table reference, index )

I suspect that the last two forms based on the Column() function will be useful in your case.

Learn it once, use it forever!

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

Mar 29, 2017 3:27 PM
(1236 views)

Apparently, I cannot use this syntax of

dt<< New Column( "Delta",

Numeric,

Formula( Column(dt,startcol)- Column(dt,endcol) )

);

startcol= 28( which is derived after a particular column)

and endCol = ncols( dt );

Since my data tables number of columns keeps changing after every loop, I can't use a number. Is there any other way?

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

Mar 29, 2017 1:42 PM
(1249 views)

Additionally, if you're running JMP 13, you can subscript into a data table using dt[row, column], and you can use ranges (1::4).

Here's a short example that adds a new column and sets its values to column 3 - columnn 1:

```
dt = New Table( "Table Subscripting",
New Column( "a", Set Values( [1 2 3 4 5] ) ),
New Column( "b", Set Values( [44 22 77 55 99] ) ),
New Column( "c", Set Values( [59, 58, 57, 56, 55] ) )
);
dt << New Column( "new column" );
dt[1::nrow(dt), 4] = dt[1::nrow(dt), 3] - dt[1::nrow(dt), 1];
```

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

Mar 29, 2017 3:25 PM
(1240 views)

dt[1::nrow(dt), endcol+1] = dt[1::nrow(dt), startcol] - dt[1::nrow(dt), endcol];

where endCol = ncols( kdt ); and startcol= 28( This is derived after a particular column)

I am trying to make everything dynamic.

I don't think this works. What your your thoughts on this Melanie?

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

Mar 30, 2017 2:36 AM
(2410 views)

This example shows how variables can be used for defining columns dynamically when 1) using the matrix notation for tables in JMP 13 or 2) setting a formula.

```
dt = New Table("Table Subscripting",
New Column("a", Set Values([1 2 3 4 5])),
New Column("b", Set Values([44 22 77 55 99])),
New Column("c", Set Values([59, 58, 57, 56, 55]))
);
startCol = 1;
endCol = N Col(dt);
// New column with the diff between endCol and startCol can be filled
// 1) with static values using matrix notation (JMP 13),
dt << New Column("Delta");
dt[0, endCol + 1] = dt[0, endCol] - dt[0, startCol];
// 2) or by a Column Formula
// (expression must partly be pre-evaluated if variables are used)
F = Eval Expr(Expr(Column(endCol))[] - Expr(Column(startCol))[]);
dt << New Column("Delta2", Formula(Name Expr(F)));
```