513082_en-US

liliconcom

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

This option or function would make it simple to create a custom column transformation to solve the problem described by @hogi in one of the later replies to count unique 

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

Investigation of this issue is on-going, and will update you if we arrive at a solution.  

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

Another example: writing a custom smoother function like a Savitzky–Golay filter that ignores excluded rows.  This can be efficiently calculated for the whole column at once when the first row evaluates, I would like to cache the values for the whole column on the first evaluation and then just return the pre-calculated result in each row instead of re-calculating it every time.

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

Yes, I noticed that, too, but sure if you were using it that way. It seems to be taking the "constant" part too seriously for what I would want. We'll have to investigate, but it likely needs new functionality for the dependency/rerun awareness.

For cases like yours, I normally make the formula depend on a table variable, and update the table variable each time through the loop to have a new random value.

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

Hi @XanGregg,

 

I could be missing something but I could not seem to get As Constant to re-evaluate when the table formulas are ran again.  It seems to (probably correctly) cache its value as long as the table is open. Here is a more complete examples which solves this problem using a global variable.

View more...

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

Have you seen the As Constant(x) function? That should handle the only-once part of the request.

 

Local(
 {startofweek = As Constant(
  Random Uniform(
   Col Min( :Timestamp ),
   Col Max( :Timestamp ) - In Days( 7 )
  )
 )},
 If( :timestamp > startofweek & :timestamp < startofweek + In Days( 7 ),
  1,
  0
 )
)

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

Thank you for the example and additional information @ih. Changed the status to investigating.

Re: Option or function to evaluate a custom column formula at once, or cache values between rows - Status changed to: InvestigatingRe: Option or function to evaluate a custom column formula at once, or cache values between rows

Here are a couple of potential use cases, I know there are others that I'm forgetting right now.  Many of these can be solved easily for small tables, or by using multiple columns or table variables, but providing a column context variable would make them easier.

• Use a random value in the column formula so it can be swapped out in a simulation
  

  // this value should be cached and re-used for all rows,
  // but it should change each time the column is recalculated
  startofweek = Random Uniform(
   Col Min( :Timestamp ),
   Col Max( :Timestamp ) - In Days( 7 )
  );
  If( :Timestamp > startofweek & :Timestamp < startofweek + In Days( 7 ),
   1,
   0
  );

• The number of rows since the start of the last out of spec sample.
• The time until this campaign ends
• Moving average of the last 10 samples for this product code

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

I think this post might demonstrate the problemCreating statistical custom (formula) function which has byVar 

Re: Option or function to evaluate a custom column formula at once, or cache values between rows - Status changed to: Needs Info

@ih would you be able to share an example of a data table that illustrates the problem you describe?  

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

@hogi - You are taking my response in the Tech Support case out of context here. I was referring to Custom Transforms since that's what you asked about.

 

I didn't say anything about custom functions in general.

 

Within the formula editor itself, there are usually ways to pass a column reference to a custom function, for example wrapping it in Expr(). Or ways for JMP to recognize that it should use a the column, for example in message sending.

 

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

ouch - the answer is even sadder: *)
 

 

*) TS-00063463

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

---

@ih wrote:

The formula would also need access to the whole column instead of just the current row, so the column reference should be passed unevaluated.

---

@Jeff_Perkinson , as for TS-00063463, I think the sad answer is: not yet.

So, besides speed, it's just that many tasks are just impossible to be implemented via custom functions at the moment.

 

status: Investigating -> sounds promising

I hope there is a way to tune Custom Functions to accept column references as a parameter in one of the next releases ...

https://community.jmp.com/t5/Discussions/Column-reference-in-custom-function/m-p/265085/highlight/true#M51645

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

To be specific, I need this functionality for custom functions which are added via:

 add custom functions (new custom function ())

Custom Transforms are just one of the applications of this wonderful framework.

edit:

e.g. 

Names Default To Here( 1 );

Add Custom Functions(
	New Custom Function(
		"COL",
		"Median",
		Function( {value, byGroup},
			Col Median( value, byGroup )
		),
		<<FORMULA category=""></FORMULA>

https://community.jmp.com/t5/Discussions/Fast-Way-to-calculate-quot-nested-quot-Col-aggregations-like-MAD/m-p/887467/highlight/true#M104961 

Option or function to evaluate a custom column formula at once, or cache values between rows

Some built in column formulas like Col Mean() are very fast because they only calculate expensive values once and return or modify them for each row.  With custom formulas this is harder to achieve because each row is evaluated separately.  Sometimes using a separate column to store intermediate values can speed things up (for example storing the row indexes that meet a certain criteria, instead of re-calculating them every row), but that gets messy and hard to follow. If there were some concept of a column context such that the evaluation of one row could access intermediate values calculated in the previous row, then a script could calculate results for all rows at once and then quickly return them and pass the intermediate results to the next row. The formula would also need access to the whole column instead of just the current row, so the column reference should be passed unevaluated.

Automation and ScriptingRe: Option or function to evaluate a custom column formula at once, or cache values between rows

Half of a solution - or let's say 1/4:

 

is a quite useful feature which I learned some while ago from a solution provided by @txnelson :
https://community.jmp.com/t5/JMP-Wish-List/Option-or-function-to-evaluate-a-custom-column-formula-at-once/idc-p/695311/highlight/true#M5011 

 

If you know that the one-time part will always give the same result, you could consider using As Constant a custom function.

 

(below code adapted from https://community.jmp.com/t5/Discussions/Differentiate-between-entire-column-and-current-row/m-p/677710/highlight/true#M86405 by @jthi )

The interesting thing: it will just be evaluated once -  and never again (!) 

In general, this will be a too extreme version of "once"

 

Any possibility to change the behavior of As Constant in a Custom Formula ? 

E.g. run it every time row()==1?

 

 

Names Default To Here(1);

row_count = 10;
dt = New Table("test",
 Add Rows(row_count),
 New Column("Die X", set each value(row())),
 New Column("Die Y", set each value(Random Integer(1, row_count * 2)))
);


dt << New Column("Formula As Constant", Numeric, Nominal, Formula(
 As Constant(print("direct","");x_m = :Die X << get values);
 Contains(x_m, :Die Y) > 0;
));


Add Custom Functions(
 New Custom Function(
  "test",
  "myFunc",
  Function( {},
   As Constant(print("custom","");x_m2 = :Die X << get values);
   Contains(x_m2, :Die Y) > 0;
)));

dt << New Column("Formula via Custom Formula", Numeric, Nominal, Formula(test:myFunc()));

 

 

 

Re: Option or function to evaluate a custom column formula at once, or cache values between rows - Status changed to: AcknowledgedRe: Option or function to evaluate a custom column formula at once, or cache values between rows

With JMP19, there will be some improvements:
e.g. it will be possible to calculate Col MAD() orders of magnitude faster - even without intermediate dummy columns:
Fast Way to calculate "nested" Col ... aggregations - like MAD 

Re: Option or function to evaluate a custom column formula at once, or cache values between rows

This holds for the type of custom functions which are manually created in Formula Editor - and with a trick for custom functions which are created via Function().
Doesn't hold or "Custom Functions" which are created via add custom functions (new custom function ().