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

Formula doesn't work within a FOR loop

Emmanuel_Romeu

Hello,

working with a customer which aims to automate data gathering from various clinical study we used Action recorder to get the appropriate lines of script.

Now I'm simplifying the script using list and loops to make it generic for larger usage.

I was able to do it down to row 58. At rows 58-61 I try to reproduce what happens with rows 63-100 using a FOR loop and a formula as shown below:

For( i = 1, i <= N Items( colList ), i++,
	Data Table( "Summary with Dose=0" ) <<
	New Column( colList_Sum_3[i],
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		formula( colList_Sum_1[i] - colList_Sum_2[i] )
	)
);

It creates the appropriate colums (based on a list) but not the formulas in each column.

I tried with Eval / Expr but I have to admit that I don't know how and when to use them.

Thanks for the help.

Emmanuel

Script to average and make difference 3.jsl

Maindata (2022 compil).jmp

Accepted answers

Jason_Wiggins

Sorry about that, Emanuel. Perhaps not enough coffee. The syntax I intended is :As Column(). I did a check on final table and the formula seems to be differencing the columns as expected. There may be a better solution that leaves a formula in the new column that makes sense in the formula editor. Is it important to have a column formula or can you build a matrix and set values?

For( i = 1, i <= N Items( colList ), i++,
	
	Data Table( "Summary with Dose=0" ) << New Column( 
		colList_Sum_3[i],
		Numeric,
		"Continuous", 
		Format( "Best", 12 ),
		formula(:As Column(colList_Sum_1[i])-:As column(colList_Sum_2[i]))		
	)
);

Jason_Wiggins

//Matrix Version
For( i = 1, i <= N Items( colList ), i++,
	
	colMat_1 = Column(colList_Sum_1[i]) << Get As Matrix;
	colMat_2 = Column(colList_Sum_2[i]) << Get As Matrix;
	colDiff = colMat_1 - colMat_2;
	
	Data Table( "Summary with Dose=0" ) << New Column( 
		colList_Sum_3[i],
		Numeric,
		"Continuous", 
		Format( "Best", 12 ),
		<< Set Values(colDiff)		
	);
);

I personally like working with matrices whenever I can but in this case you will loose the column formula. Don very likely has a better way using expression handling if you want to keep the column formula in the new table. Attached is a white paper Joseph Morgan wrote on expression handling. It is dense and takes several reads to parse but it is good. Don will have some other stuff that may be easier to digest:) Enjoy!

JosephMorganExpressionHandlingPart1.pdf

DonMcCormack

I apologize for my earlier posts, which were a bit hasty as I didn't properly read through your code and tried to rush a solution. This is a challenging problem. I doubt it can be done without expression handling to get the column names to appear in the formulas. The Enhance Log is a great place to start but it has some shortcomings. In particular, you want to eliminate/minimize the use of hard coded names for tables and columns. This will make your code more maintainable and robust. I have attached a solution starting with the code you provided. I've added comments to the script. If you have questions, please post. Expression handling is not an easy concept to understand. Along with Jason's links, I'm adding material that will be part of the JSL Cookbook redo (in particular, a recipe that is hopefully a gentle introduction to expression handling and the recipe it is built on). I have not had a chance to do a final edit on the recipe using expression so let me know if there's anything that would improve it. I have mp4 for each but they're too big to attach.

Script to average and make difference - with expressions.jsl

Tailored Report 2 (final).jsl

Build a Tailored Report using Messages 2.docx

Build a Tailored Report using Messages 1.docx

Tailored Report (start).jsl

Tailored Report (final).jsl

All comments

DonMcCormack

Does this do what you had in mind? You might be able to get it done without using expression handling, but I find this approach to be more robust, particularly when you have to deal with problematic column names (e.g., when you have a mathematical operator or statistical reserved word in it). The idea is to treat the two arguments to the Join message that need the columns as expressions, build them by inserting column references into them (don't use names because of the issue I mentioned above), then insert them into the Join message with Substitute (which you can put into Expr so the expression gets evaluated as well). One recurring theme is the use of Name Expr to keep the variable you're working with from evaluating completely (i.e., you want to treat the contents of the variable as an expresion, you are not interested in its evaluated value). Hope this helps.

Don

Script to average and make difference - DWMc edits.jsl

Jason_Wiggins

Hi Emmanuel,

You are only off by a bit. Try the script attached. It seems to work on your sample data. Corrections are commented as //Try this instead. Your original code is commented out.

Best,

Jason

Script to average and make difference 3_jkw.jsl

DonMcCormack

Reposted with correct script

Emmanuel_Romeu

Hi Don, did you change anything in the script ? I guess you copy/paste the original one w/o any change or I missed it.

Emmanuel_Romeu

Hi Jason,

it returns columns containing a formula : Column( colList_Sum_1[i] ) - /*###*/Column( colList_Sum_2[i] ) /*###*/

but no calculation is done.

Emmanuel

DonMcCormack

Sorry Emmanuel,

Initially posted the original script. Reposted with the edited script.

Don

Jason_Wiggins

Everything should work up to line 76 in my edited script. It seems that beyond this, you could solve the problem the same way you did with loops above? I like Don's expression handling for the join. This is worth considering. You still have to deal with the column lists and column names so perhaps you could grab ideas from both of our scripts. I will send an update later today with a fix for the lower 37 lines of your script.

Emmanuel_Romeu

Hi Jason,

in your edited script lines 69-76 does not return any calculation

For( i = 1, i <= N Items( colList ), i++,
    Data Table( "Summary with Dose=0" ) << New Column( colList_Sum_3[i],
        Numeric,
        "Continuous",
        Format( "Best", 12 ),
        formula( Column( colList_Sum_1[i] ) - Column( colList_Sum_2[i] ) )
    )
);

For clarification lines 69-76 (58-61 in the original script) aim to replace lines 78-115 (63-100 in the original script).

The rest of the script is OK it is on lines 69-76 (58-61 in the original script) that I'm stuck.

Emmanuel_Romeu

Hi Don, I've been through the e-learning on scripting but I have hard time to understand Eval, Expr, NameExpr, ... by chance would you have any complementary course, documentation which could help ?

DonMcCormack

Script to average and make difference - with expressions.jsl

Tailored Report 2 (final).jsl

Build a Tailored Report using Messages 2.docx

Build a Tailored Report using Messages 1.docx

Tailored Report (start).jsl

Tailored Report (final).jsl

Emmanuel_Romeu

Thank you Jason and Don, both solutions works.

I have to go deeper in Don's solution/example and increase my skills around Expressions.

Thanks again for the great help.

Emmanuel

brady_brady

Hi everyone,

Very late to the party here, but thought I'd add this example as it tackles things a different way. As Don rightly mentions, learning expression handling is powerful, desirable, and difficult/frustrating at times... even for those familiar well-versed in handling expressions, writing formula columns can be quite tricky. In my opinion it is one of the most difficult things in all of JSL, which is unfortunate as creating formula columns referencing dynamically-determined names is something users often must do.

I've come up with a workaround that uses JMP's ability to update formulas when the column names they reference change. The general idea is this:

1) Assign placeholder names (making sure the names you use are unique--collision with existing column names must be avoided) to any columns, existing or soon-to-exist, your formulas must reference. This allows EXPLICIT naming of these columns, WITHOUT expression handling.

2) Write code to create needed formula columns

3) Change the placeholder names to the actual column names you want. This will not require expression handling.

Steps 1-3 can be placed inside a loop if needed, as in this example.

Names Default To Here(1);
dt = current data table();
dt << show window(0);
colNames = dt << get column names(string);
nOrigCols = ncol(dt);

for each({v,i}, colNames,
	if(i > 4,
		dt << move selected columns({5}, to last);
		
		//assign a placeholder name to the current column and newly created columns, so you can be explicit when writing formulas
		column(dt, v) << set name ("aaa") << set selected;
		dt << new column("bbb", formula(colmean(:aaa, :trial_id, :trial_location, :enzyme, :dose)));
		dt << new column("ccc", formula(colsum((:dose == 0) * :aaa, :trial_id, :trial_location) /col sum(:dose == 0, :trial_id, :trial_location)));
		dt << new column("ddd", formula(:bbb - :ccc));
		
		//change the column names to the actual names needed
		:aaa << set name(v);
		:bbb << set name(evalinsert("Mean(^v^)"));
		:ccc << set name(evalinsert("Mean(^v^)_Dose == 0"));
		:ddd << set name(evalinsert("Mean(^v^)_diff"));
	);
);

//subsetting and cleanup
dt << select duplicate rows(match columns(colNames[1::4])) << invert row Selection << invert column selection;
dtSub = dt << subset(selected rows(1), selected columns(1) );
dtSub << sort(by(:trial_id, :trial_location, :enzyme, :dose), order(ascending, ascending, ascending, ascending), replace table);
for each({i}, 1::4, column(dt, i) << set selected(0));
dt << Delete Columns << show window(1) << clear select;
dtSub << Bring Window To Front;

Again, this by no means is a substitute for learning expression handling, but sometimes people just need to get things done in a pinch, without taking the time to learn something as complex as expression handling.

NOTE: in my example, the formula columns are written into the original table. If you want to see them (I later delete them), comment out the second-to-last line and un-hide the window.

Cheers,

Brady