nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Newbie help with Dialog variable to use within a formulae

Bill_McN

Hi I'm trying a basic Delta between 2 columns which I want to choice using a dialog to choose both columns and use a basic delta X-Y formulae. However, I don't seem to be able to work out the correct syntax for this. The formulae is not carrying into the new column which is being created. When I hover over the my_X and my_Y field they do bring up the correct names. Can you advice...

dt_values = Current Data Table();

cdlg = Column Dialog( yCols = ColList( "POR", MinCol( 1 ), MaxCol( 1 ) ) );

my_Y = cdlg["yCols"];

cdlg = Column Dialog( xCols = ColList( "Diff", MinCol( 1 ), MaxCol( 1 ) ) );

my_X = cdlg["xCols"];

dt_values << new column("Delta", Numeric, Format( "Fixed Dec", 10, 2 ), formula( Column(my_X[1]) ) - (Column(my_Y[1])));

Basic Table

A	B	Delta
1	3
2	12

Accepted answers

txnelson

Here is a solution to your script.

dt_values = Current Data Table();
　
cdlg = Column Dialog( yCols = ColList( "POR", MinCol( 1 ), MaxCol( 1 ) ) );
my_Y = cdlg["yCols"];
　
cdlg = Column Dialog( xCols = ColList( "Diff", MinCol( 1 ), MaxCol( 1 ) ) );
my_X = cdlg["xCols"];
　
dt_values << New Column( "Delta",
	Numeric,
	Format( "Fixed Dec", 10, 2 ),
	formula( eval(my_X[1] )  - eval( my_Y[1] ))
);

However, a better version of your code is:

dt_values = Current Data Table();
　
cdlg = Column Dialog( yCols = ColList( "POR", MinCol( 1 ), MaxCol( 1 ) ) ,xCols = ColList( "Diff", MinCol( 1 ), MaxCol( 1 ) ) );
my_Y = cdlg["yCols"];
my_X = cdlg["xCols"];
　
Eval(
Substitute(
		Expr(
			dt_values << New Column( "Delta", Numeric, Format( "Fixed Dec", 10, 2 ), formula( _x_ - _y_ ) )
		),
	Expr( _x_ ), Parse( ":" || Char( my_X[1] ) ),
	Expr( _y_ ), Parse( ";" || Char( my_Y[1] ) )
));

A final comment: JMP has downgraded the value of the Column Dialog function, and points users to use a "Modal" New Window() function. You can see illustrations on how to do this in:

Help==>Books==>Scripting Guide

All comments

Bill_Worley

Hi Bill,

You can generate a new difference column by highlighting the two columns of interest and then right click in the column header. Go to the New Formula Column tab and click to follow the prompts. An example is shown below.

HTH

Bill W.

Bill_McN

Sorry using JMP 11.1, and that option is not available.

txnelson

Here is a solution to your script.

dt_values = Current Data Table();
　
cdlg = Column Dialog( yCols = ColList( "POR", MinCol( 1 ), MaxCol( 1 ) ) );
my_Y = cdlg["yCols"];
　
cdlg = Column Dialog( xCols = ColList( "Diff", MinCol( 1 ), MaxCol( 1 ) ) );
my_X = cdlg["xCols"];
　
dt_values << New Column( "Delta",
	Numeric,
	Format( "Fixed Dec", 10, 2 ),
	formula( eval(my_X[1] )  - eval( my_Y[1] ))
);

However, a better version of your code is:

dt_values = Current Data Table();
　
cdlg = Column Dialog( yCols = ColList( "POR", MinCol( 1 ), MaxCol( 1 ) ) ,xCols = ColList( "Diff", MinCol( 1 ), MaxCol( 1 ) ) );
my_Y = cdlg["yCols"];
my_X = cdlg["xCols"];
　
Eval(
Substitute(
		Expr(
			dt_values << New Column( "Delta", Numeric, Format( "Fixed Dec", 10, 2 ), formula( _x_ - _y_ ) )
		),
	Expr( _x_ ), Parse( ":" || Char( my_X[1] ) ),
	Expr( _y_ ), Parse( ";" || Char( my_Y[1] ) )
));

A final comment: JMP has downgraded the value of the Column Dialog function, and points users to use a "Modal" New Window() function. You can see illustrations on how to do this in:

Help==>Books==>Scripting Guide

Bill_McN

Thanks for both codes, they both worked. I've used the 2nd

I'll review the modal as per your recommendation, but curious as to whether the dialog will still work in future as many scripts currently use the dialog functionality at moment.