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
- :
- Create New Column with variables

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

Nov 27, 2013 10:17 PM
(1081 views)

Hi all,

I am trying to create a new column with formula, which represented by 2 variables as below:

dt << New Column("myNewColumn", Numeric, Continuous, Formula(:Ceiling( XXColumn / Size) * Size));

where:

XXColumn is one of column name in dt, selected in column dialog

Size = numeric value from column dialog

My attempt resulted a new column with formula with no value as formula create as below:

Ceiling(Column(XXColumn ) / Size) * Size

without actually assign it.

Any idea how I can accomplish this?

1 ACCEPTED SOLUTION

Accepted Solutions

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

Solution

finally the magic happened !!!

here is the code i used..

col = dts << New Column("Binning", Numeric, Continuous);

Eval( Parse( Eval Insert( "col << Set Formula(:Ceiling( ^n_Bin^ / ^BinSize^) * ^BinSize^)<< eval formula" )) );

Thank You MS for the guidance; what is the diff on f_bin and n_bin i used?

12 REPLIES

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

Nov 27, 2013 11:26 PM
(593 views)

Variables in formulas are not evaluated when created and should therefore be expressed literally. One way to circumvent that is to manipulate and parse strings.

Example:

dt = Open**(** "$SAMPLE_DATA/Big Class.jmp" **)**;

cols = Column Dialog**(** Col1 = ColList**(** "X", Max Col**(** **1** **)**, Datatype**(** numeric **)** **)**, Col2 = ColList**(** "Y", Max Col**(** **1** **)**, , Datatype**(** numeric **)** **)** **)**;

XXColumn = cols**[**"Col1"**][****1****]** << get name;

Size = cols**[**"Col2"**][****1****]** << get name;

Eval**(** Parse**(** Eval Insert**(** "dt << New Column(\!"myNewColumn\!", Numeric, Continuous, Formula(Ceiling( ^XXColumn^ / ^Size^) * ^Size^))" **)** **)** **)**;

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

Nov 27, 2013 11:45 PM
(593 views)

Thank you MS.

The new column created with correct formula. But, the rows are empty. I need to go to the formula section on UI and click apply then only values prompts.

is it expected?

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

Nov 28, 2013 12:25 AM
(593 views)

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

Nov 28, 2013 1:05 AM
(593 views)

mine is JMP8.0.0 Windows

with or without it still the same. T_T

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

Nov 28, 2013 1:46 AM
(593 views)

Try <<eval formula.

Eval**(** Parse**(** Eval Insert**(** "dt << New Column(\!"myNewColumn\!", Numeric, Continuous, Formula(Ceiling( ^XXColumn^ / ^Size^) * ^Size^), <<eval formula)" **)** **)** **)**;

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

Nov 28, 2013 2:44 AM
(593 views)

Its not working. The result still same.

In JSL, apply button == ? do you know?

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

Nov 28, 2013 2:57 AM
(593 views)

No. Eval Formula is supposed to be eqivalent as far s I know.

Try to separate the new column and set formula into separate expressions:

dt = Open**(** "$SAMPLE_DATA/Big Class.jmp" **)**;

cols = Column Dialog**(**

Col1 = ColList**(** "X", Max Col**(** **1** **)**, Datatype**(** numeric **)** **)**,

Col2 = ColList**(** "Y", Max Col**(** **1** **)**, Datatype**(** numeric **)** **)**

**)**;

XXColumn = cols**[**"Col1"**][****1****]** << get name;

Size = cols**[**"Col2"**][****1****]** << get name;

col = dt << New Column**(** "myNewColumn", Numeric, Continuous **)**;

Eval**(** Parse**(** Eval Insert**(** "col << Set Formula(Ceiling( ^XXColumn^ / ^Size^) * ^Size^)" **)** **)** **)**;

col << eval formula;

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

Nov 28, 2013 3:10 AM
(593 views)

It did not work. Maybe I should explain what I am doing and share paste my code.

Step 1: Collect input from Column dialog

Step 2: Do Summary table with Min,Mean, Max for selected column + default column

Step 3: Create new column for Ceiling

Step 4: Do plotting using Graph builder

Code:

dt0 = current data table();

FN = dt0 << Get Name;

r = Column Dialog(

Bin = ColList( "X, Binning By", Max Col( 1 ) ),

Overlay = ColList( "Group by / Overlay by", Max Col( 1 ) ),

Split = ColList( "Y, Response",Max Col( 5 ) ),

Compare = ColList( "X-Axis Top", Max Col( 1 ) ),

HList( "Bin Size: ", BinSize = EditNumber( .1 ) ),

);

Bin = r["Bin"];

Overlay = r["Overlay"];

Split = r["Split"];

Compare = r["Compare"];

BinSize = r["BinSize"];

dts = dt0 << Summary(

Group(:Fixed, column(dt0,Overlay), column(dt0,Compare) ),

Mean( :Yield ),

Min ( column(dt0,Bin) ),

Mean( column(dt0,Bin) ),

Max ( column(dt0,Bin) ),

Min ( column(dt0,Split[1]) ),

Mean( column(dt0,Split[1]) ),

Max ( column(dt0,Split[1]) ),

Min ( column(dt0,Split[2]) ),

Mean( column(dt0,Split[2]) ),

Max ( column(dt0,Split[2]) ),

Min ( column(dt0,Split[3]) ),

Mean( column(dt0,Split[3]) ),

Max ( column(dt0,Split[3]) ),

Min ( column(dt0,Split[4]) ),

Mean( column(dt0,Split[4]) ),

Max ( column(dt0,Split[4]) ),

Min ( column(dt0,Split[5]) ),

Mean( column(dt0,Split[5]) ),

Max ( column(dt0,Split[5]) ),

statistics column name format( "column stat" ),

);

dts << Set Name (FN || " Summary");

n_Bin = Bin[1]||" Mean";

f_Bin = Column(dts, n_Bin);

col = dts << New Column("Binning", Numeric, Continuous);

Eval( Parse( Eval Insert( "col << Set Formula(Ceiling( ^f_Bin^ / ^BinSize^) * ^BinSize^)" )) );

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

Nov 28, 2013 3:55 AM
(593 views)

Try use n_Bin directly instead of f_Bin:

Eval**(** Parse**(** Eval Insert**(** "col << Set Formula(Ceiling( ^n_Bin^ / ^BinSize^) * ^BinSize^)" **))** **)**;