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
- :
- 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
(3186 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

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

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

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

No, it was not expected. For me (JMP 10 for Mac) it works without clicking "Apply". There was an inadvertant colon before "Ceiling..." that remained from your original code (I've now edited my code above). Maybe it helps to delete that.

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

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

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

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

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

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

Try use n_Bin directly instead of f_Bin:

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