Subscribe Bookmark RSS Feed

Column difference- JSL syntax

vishwasanj

Frequent Contributor

Joined:

Dec 27, 2016

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
ms

Super User

Joined:

Jun 23, 2011

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
markbailey

Staff

Joined:

Jun 23, 2011

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!
vishwasanj

Frequent Contributor

Joined:

Dec 27, 2016

Hi Mark,

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?
melaniedrake

Staff

Joined:

May 30, 2014

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];
vishwasanj

Frequent Contributor

Joined:

Dec 27, 2016

Does 4 automatically imply it's the end of the table? Since the data table columns keeps changing , can i use
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?
ms

Super User

Joined:

Jun 23, 2011

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)));