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

How to change a formula column into a variable?

Thomas1

I'm using a first formula column as a interim result, in order to recalculate the conclusively result with a second formal column. The second column is using the result from the first formula column.

Is there any easy way to avoid the first formula column, by creating a varaibele for example? My JSL knolodge is very basic.

Thanks in advance.

Accepted answers

Jeff_Perkinson

Thanks for posting your sample data table.

Since you want to calculate quantiles for only some rows you'll have to have a new formula based column. Since you're already doing that with dataTmp that's a fine way to continue. However, you don't need flyerTmp since all that's doing is a conditional that you then use in dataCorr. You can just put that condition directly into dataCorr.

I've done this in the attached data table with 100,100 rows. It's pretty fast.

I'm curious why you were concerned about getting rid of the intermediate column? Intellectual curiosity or some other concern?

34345.jmp

All comments

cwillden

Can you just copy the formula from the first column, and paste it into the formula for the second column wherever the first column is referenced?

Thomas1

Thanks for the solution. It works. However this can slow down the calculation in case some if states are being used within the formula.

cwillden

You'll have to post a working example if you want more precise help.

Byron_JMP

Just checking on a couple of details:

1. is the value the same for all of the rows in the first column?

2. Are you scripting the the addtion of columns?

-What might be possible is to have the first result calculated in memory, as a single column in a matrix, then, if you want those values, just get them from the matrix and make a new column with those values. Often this is faster than adding a colum and wating for it up update in the table (for really tall tables).

-Better yet, do both calculations in a matrix (in memory, not in the table) and then post the results back to the table.

-If all the values are the same in column 1, then maybe just calculate this in a script, and add it to the data table as a table

variable and reference the variable in column 2.

lots of good options, but they all depend on what you're trying to do.

Thomas1

Hi Cameron,

attached the file example.jmp. What is the best way to avoid the creation of the colums :DataTmp and :FlyerTmp in order to get the colums :DataCorr with the help of the colums :P1, :P2 and :DataRaw?

This sould be applicable on data sets with more than 100,000 rows.

Thomas

example.jmp

Thomas1

I did upload the file example.jmp. My specific question is, what is the best way to avoid the creation of the colums :DataTmp and :FlyerTmp in order to get the colums :DataCorr with the help of the colums :P1, :P2 and :DataRaw?

According to JSL users it is better to calculate these colums with the help of lists or martizes, especially if the data sets are big.