Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted

## Optimization of linear expression using columns to solve for variables

Is there a way to use Minimize() or a similar function to solve for this expression:

``expr1 = expr((a:A+b:B+c:C)=:Target);``

using the following table:

 A B C Target 10 0 0 30 500 0 0 1500 9999 0 0 29997 30 0 100 590 0 50 9999 500095 0 1000 500 4500 50 9999 0 20148 200 100 0 800 10 0 0 30 0 0 0 0
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## Re: Optimization of linear expression using columns to solve for variables

I'm not sure what you are really trying to do, but this looks like a regression problem to me:

Find values of a, b, and c such that the difference between (a*A + b*B + c*C) (which I will call predicted value) and Target is as small as possible. In the regression context that would be minimize the sum of squared errors where an error = Target - Predicted Value.

You can do this interactively with Fit Model, which I did. The script from going that is down below. The result is: ``````Fit Model(
Y( :Target ),
Effects( :A, :B, :C ),
Personality( "Standard Least Squares" ),
Emphasis( "Effect Leverage" ),
Run(
:Target << {Summary of Fit( 1 ), Analysis of Variance( 1 ),
Parameter Estimates( 1 ), Scaled Estimates( 0 ),
Plot Actual by Predicted( 1 ), Plot Regression( 0 ),
Plot Residual by Predicted( 1 ), Plot Studentized Residuals( 0 ),
Plot Effect Leverage( 1 ), Plot Residual by Normal Quantiles( 0 ),
Box Cox Y Transformation( 0 )}
)
);
``````

Dan Obermiller
10 REPLIES 10
Highlighted

## Re: Optimization of linear expression using columns to solve for variables

Sorry, error with expression:

dt = Current Data Table;
expr1 = expr((a*dt:A+b*dt:B+c*dt:C)=:Target);

Looking to identify values for a, b, and c.
Highlighted

## Re: Optimization of linear expression using columns to solve for variables

I'm not sure what you are really trying to do, but this looks like a regression problem to me:

Find values of a, b, and c such that the difference between (a*A + b*B + c*C) (which I will call predicted value) and Target is as small as possible. In the regression context that would be minimize the sum of squared errors where an error = Target - Predicted Value.

You can do this interactively with Fit Model, which I did. The script from going that is down below. The result is: ``````Fit Model(
Y( :Target ),
Effects( :A, :B, :C ),
Personality( "Standard Least Squares" ),
Emphasis( "Effect Leverage" ),
Run(
:Target << {Summary of Fit( 1 ), Analysis of Variance( 1 ),
Parameter Estimates( 1 ), Scaled Estimates( 0 ),
Plot Actual by Predicted( 1 ), Plot Regression( 0 ),
Plot Residual by Predicted( 1 ), Plot Studentized Residuals( 0 ),
Plot Effect Leverage( 1 ), Plot Residual by Normal Quantiles( 0 ),
Box Cox Y Transformation( 0 )}
)
);
``````

Dan Obermiller
Highlighted

## Re: Optimization of linear expression using columns to solve for variables

Hi @Dan , thanks for the response. This seems promising for what I need, although how can you pull out the result that you mentioned? Also, how would pull out those individual values for a, b, and c?

Thanks!

Highlighted

## Re: Optimization of linear expression using columns to solve for variables

The results would be in the Parameter Estimates report. If you are trying to pull them out via JSL, look at the JMP Scripting Guide. Probably start with the section "Navigate JMP Reports".

Dan Obermiller
Highlighted

## Re: Optimization of linear expression using columns to solve for variables

I've had good luck with the nonlinear platform (even for linear optimization).  You need to enter parameters in conjunction with columns and it will solve for those parameters.

Highlighted

## Re: Optimization of linear expression using columns to solve for variables

The phrasing of the original question perhaps suggests that a solution via JSL is required. If that's the case, an alternative avoiding the use of JMP Platforms is:

``````NamesDefaultToHere(1);

// Data table
dt = New Table( "Test",
New Column( "A",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [10, 500, 9999, 30, 0, 0, 50, 200, 10, 0] )
),
New Column( "B",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 0, 0, 50, 1000, 9999, 100, 0, 0] )
),
New Column( "C",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [0, 0, 0, 100, 9999, 500, 0, 0, 0, 0] )
),
New Column( "Target",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [30, 1500, 29997, 590, 500095, 4500, 20148, 800, 30, 0] )
)
);

// Get values into vectors (could use table subscripting too)
Y = Column(dt, "Target") << getValues;
Avals = Column(dt, "A") << getValues;
Bvals = Column(dt, "B") << getValues;
Cvals = Column(dt, "C") << getValues;
X = Avals||Bvals||Cvals;

// Do the regression (see 'Help > Scripting Index' for more details of 'results')
results = LinearRegression(Y, X);
parameters = results;
Print(parameters);``````

But as @Dan_Obermiller points out, as stated it can be viewed as a regression problem, and it's generally not a good idea to do regression without a visual inspection of the data or the results.

Highlighted

## Re: Optimization of linear expression using columns to solve for variables

While this method seems promising, I'm running into problems scaling it up... The log outputs:

Inputs have missing values.

[., ., ., ., ., ., ., ., ., ., .]

I'm attempting to eventually scale this up to a ~20 variable problem, where

X = [20x200] matrix

Y = [1x200] matrix

Y = M*X , where M is a matrix of 20 variables

Is this still possible with linear regression? Some online resources seem to suggest batch gradient descent.

Thanks

Highlighted

## Re: Optimization of linear expression using columns to solve for variables

You might want to look at the free Statistical Thinking for Industrial Problem Solving course (or just Statistical Thinking course for short) that is available from the Learn JMP tab. Look at the regression section. That will show you how regression works and how to do it in JMP. Regression can handle a problem of this size.

Have your X columns entered into a JMP table. You will have 20 X columns and 200 rows. Put your Y column in as well so that your table has a total of 21 columns. From there you can use any of the JSL code if you want to use JMP scripting. Alternatively, you can use Fit Model. Specify your Y. Highlight all of your X's and click Add. Then click Run Model.

Dan Obermiller
Highlighted

## Re: Optimization of linear expression using columns to solve for variables

Great, thank you, the Fit Model seems to work well. I'll also look into that class to learn more.

I take it the "Parameter Estimates" table in the fit model output are the number I'm looking for. Is there a way to extract that table into a data table using JSL?

Article Labels

There are no labels assigned to this post.