cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
thomasz
Level IV

Column reference in custom function

Having this table:

New Table( "test table",
	Add Rows( 9 ),
	New Column( "By column",
		Character( 1 ),
		"Nominal",
		Set Selected,
		Set Values( {"a", "a", "a", "b", "b", "b", "c", "c", "c"} )
	),
	New Column( "Categories",
		Character( 1 ),
		"Nominal",
		Set Values( {"t", "t", "r", "y", "t", "r", "r", "r", "r"} )
	),
	New Column( "N Categories",
		Expression,
		"None",
		Formula(
			If( Row() == 1,
				Summarize( x = by( :By column, :Categories ) ),
				Empty()
			);
			N Items( Loc( x[1], :By column ) );
		),
		Set Display Width( 109 )
	),
	Set Row States( [1, 0, 0, 0, 0, 0, 0, 0, 0] )
)

I would like to create a custom function doing the same thing as the formula in the N Categories column.

Converting it into a custom function script in the formula editor gives me something like this:

Add Custom Functions(
	{New Custom Function(
		"custom",
		"N Categories",
		Function( {Categories,By column},
			{Default Local},
			If( Row() == 1,
				Summarize( x = by( By column, Categories ) ),
				Empty()
			);
			N Items( Loc( x[1], By column ) );
		),
		<<Description( "" ),
		<<Prototype( "" ),
		<<Formula Category( "" ),
		<<Scripting Index Category( "" ),
		<<Result Type( {"Any"} ),
		<<Parameter( {"Any"}, "" ),
		<<Parameter( {"Any"}, "" )
	)}
);

But now the parameters, prior being column references just become parameters containing the values of the rows.

So my question is: Is it possible to create a custom function that treats the parameters as column references?

I could make it work by treating the parameters as column names and transfer strings instead, but it would not be the same!

7 REPLIES 7
txnelson
Super User

Re: Column reference in custom function

1. For all of the Community Members that are confused and looking for "Add Custom Functions()", this is a feature that is being added to JMP 14.

2. @thomasz, I don't think you can expect the function you are building to be identical to the formula.  Your formula is not a generic formula, it is a hard coded formula, unlike what you are attempting to do with the function.  So there are naturally going to have to be differences. I think you need to think in terms of what would the formula look like, if it was a generic function, and then develop the formula as such.  See below for a similar generic formula that I put together.

custom function.PNG

Jim
thomasz
Level IV

Re: Column reference in custom function

Thanks for your reply. I realized afterwards there are some other problems like x cannot be local, but this can be fixed.

But I couldn't figure out how to pass a column reference in the function call. I had a similar problem some time ago and solved it passing column names as strings. As in the example below that works:

names default to here(1);
Add Custom Functions(
	{New Custom Function(
		"NNE",
		"N Categories",
		Function( {Categories, ByColumn},{Default local},
			If( Row() == 1,
				Summarize( here:x = by( column(ByColumn), column(Categories) ) );
			);
			N Items( Loc( here:x[1], column(ByColumn)[row()] ) );
		),
		<<Description( "Determine number of unique categories of a column by/within each By Column" ),
		<<Prototype( "N Categories( :Categories, :By column )" ),
		<<Formula Category( "NNE" ),
		<<Scripting Index Category( "" ),
		<<Result Type( {"Any"} ),
		<<Parameter( {"String"}, "Category Column name" ),
		<<Parameter( {"String"}, "By Column name" ),
		<<Example(expr(dt = Open( "$SAMPLE_DATA/Air Traffic.jmp" );	
dt<<new column("Different Airlines at Airport",formula(NNE:N Categories("Airline","Airport")));

));
	)}
);

It can be called like this in a column formula:

image.png

But it requires strings instead of column references. I can rephrase my question: Is it somehow possible to change the above so that the N Categories function accepts column references instead of strings?

 

john_madden
Level VI

Re: Column reference in custom function

I'd like to re-pose this question, as I too have struggled with this issue for -- it seems like forever. 

Does anyone have a reliable way of writing a custom function (user function) such that it accepts a column reference (e.g. :Column 1) as a parameter?

My workaround has always been to pass the column name as a string and then, inside the function, use Column() or As Column(), depending on circumstances, to generate the reference.

But if I want to write custom functions that work cleanly in column formulas for naïve users who aren't expecting this workaround, it would be nice to be able to pass column reference instead or column name (just as one does, or so it seems, for native functions).

John

 

john_madden
Level VI

Re: Column reference in custom function

I'll just say, the best I have so far been able to come up with -- and it isn't really what I want -- is to enclose the column parameter in a Name Expr() instruction. For example, given a data table with a column named ":Column 1" this works:

Names Default To Here( 1 );
dt = Current Data Table();
Add Custom Functions(
	New Custom Function(
		"x",
		"Testf",
		Function( {col},
			{i},
			For Each Row( dt, Print( col ) );
		)
	)
);
x:Testf(Name Expr(dt:Column 1));

but you can't do this:

dt = Current Data Table();
Add Custom Functions(
	New Custom Function(
		"x",
		"Testf",
		Function( {Name Expr(col)}, // JSL interpreter gives error for this syntax
			{i},
			For Each Row( dt, Print( col ) );
		)
	)
);
x:Testf(dt:Column 1); // hypothetical, as the function definition is in error
hogi
Level XI

Re: Column reference in custom function

Seems that I got stuck with the same question:
Custom Function - how to reference the column 

... even the same application case.

 

Concerning count unique, there is a wish list entry waiting for Kudos:
new JSL function: Col N Categories 

 

Concerning the issue with referencing a column, there is a wish - actually related, but different subject:
Option or function to evaluate a custom column formula at once, or cache values between rows 
... but nevertheless, it mentions the issue with referencing a column in the last sentence:

hogi_0-1699219690695.png

 

hogi
Level XI

Re: Column reference in custom function

Concerning Plan B, referencing a column, I just got this reply from Jmp Support TS-00063463

 

hogi_0-1699302254919.png

 

 

hogi
Level XI

Re: Column reference in custom function


@john_madden wrote:

But if I want to write custom functions that work cleanly in column formulas for naïve users who aren't expecting this workaround


Besides naive users, it's also Jmp itself: "transform functions" where the available workaround solutions fail.