topic Re: Column reference in custom function in Discussions

Column reference in custom function

thomasz — Fri, 16 Mar 2018 19:57:46 GMT

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!

Re: Column reference in custom function

txnelson — Sat, 17 Mar 2018 11:08:45 GMT

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.

Re: Column reference in custom function

thomasz — Sat, 17 Mar 2018 22:39:17 GMT

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:

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?

Re: Column reference in custom function

john_madden — Thu, 07 May 2020 17:39:42 GMT

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

Re: Column reference in custom function

john_madden — Fri, 08 May 2020 13:32:10 GMT

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

Re: Column reference in custom function

hogi — Sun, 05 Nov 2023 21:29:03 GMT

Seems that I got stuck with the same question:

... even the same application case.

Concerning count unique, there is a wish list entry waiting for Kudos:

Concerning the issue with referencing a column, there is a wish - actually related, but different subject:

... but nevertheless, it mentions the issue with referencing a column in the last sentence:

Re: Column reference in custom function

hogi — Mon, 06 Nov 2023 20:25:33 GMT

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

Re: Column reference in custom function

hogi — Tue, 07 Nov 2023 07:21:10 GMT

@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.