How to change a formula column into a variable?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.
Re: How to change a formula column into a variable?Thanks for your reply.
Could you show me how to combine the col quantile() row function with the matrix version?
Re: How to change a formula column into a variable?As you said the If satement formula works very well in order to get the NewDataCorr values.
I did update your file with the new column :NewDataCorr 2. In this column your If statement formula has been used. Instead of using the values from DataTmp column, I did express them with the DataTmp formula:
If( :P1 == 0 & :P2 < 3,
:DataRaw
)
In this case I got rid off the DataTmp column. What is your opinion about this solution?
Re: How to change a formula column into a variable?Cameron's solution should be pretty fast, even on 100K+ rows.
One other thing, with the col quantile() function gets evaluated for each row in the data table formula, and that would slow things down a little too. In the matrix version, it only gets evaluated once.
Re: How to change a formula column into a variable?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.
Re: How to change a formula column into a variable?Thank you for your JSL suggestion. I'm curious to test the script. Right now I'm not able to check tihis (no acess to JMP).
I'm wondering how are data pointsbeing treated from the origin Column DataRaw if they are 0?
Re: How to change a formula column into a variable?Thanks for your reply.
I want to get rid off the tmp columns in order to keep the data set as simple as possible. The example file is a very basic file regarding my question. In practice the data set contains about 10 to 15 different DataRaw columns, which I like to prepare in the same way. There are also other different columns added.
You are right about the FlyerTmp column. It can be included within the DataCorr column. I will check your file later (no access to JMP right now).
Re: How to change a formula column into a variable?Just thought of something. You'd have to remove the zeros from temp before computing the quantile since those zeros will be included. You can do that by running this before x98.
miss_rows = loc(as list(temp),0);
temp[miss_rows] = .;
You could do the same for "final" as well before creating the new column. That would get rid of the need to replace the missing values at the end.
Re: How to change a formula column into a variable?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?
Re: How to change a formula column into a variable?Try something like this. The idea is doing element-wise multiplication of the data with vectors of boolean values, which are your logical conditions. This should be pretty fast, but I admit I'm far from an expert on what will be computationally most efficient.
dt = Current Data Table();
//Get values for other columns
raw = dt:DataRaw << Get Values;
P1vals = dt:P1 << Get Values;
P2vals = dt:P2 << Get Values;
//Compute equivalent of DataTmp
temp = (P1vals == 0 & P2vals < 3):*raw;
//Compute 98th percentile of temp
x98 = quantile(0.98, temp);
//Compute final column
final = (temp <= x98):*temp;
dt << New Column("DataCorr", << Set Values(final));The result will have 0's instead of missing. You can replace them with missing values with something like this (though if you still have the original DataCorr in your table, the previous script will produce a column "DataCorr 2" and this code line will not be working on the right column).
//Replace 0s with missing
dt:DataCorr[dt << Get Rows Where(:DataCorr == 0)] = .;
Re: How to change a formula column into a variable?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
Re: How to change a formula column into a variable?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.
Re: How to change a formula column into a variable?You'll have to post a working example if you want more precise help.
Re: How to change a formula column into a variable?Thanks for the solution. It works. However this can slow down the calculation in case some if states are being used within the formula.
Re: How to change a formula column into a variable?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?
