457425_en-US

liliconcom

Formula doesn't work within a FOR loop

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

Re: Formula doesn't work within a FOR loop

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

Re: Formula doesn't work within a FOR loop

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

Re: Formula doesn't work within a FOR loop

Reposted with correct script

Re: Formula doesn't work within a FOR loop

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.

Re: Formula doesn't work within a FOR loop

Hi Jason,

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

but no calculation is done.

Emmanuel

Re: Formula doesn't work within a FOR loop

Sorry Emmanuel,

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

Don

Re: Formula doesn't work within a FOR loop

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.

 

Re: Formula doesn't work within a FOR loop

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.