Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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!

4 REPLIES 4
Highlighted
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
Highlighted
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?

 

Highlighted
john_madden
Level V

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

 

Highlighted
john_madden
Level V

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
Article Labels

    There are no labels assigned to this post.