Subscribe Bookmark RSS Feed

Using Row() in a user function

john_madden

Community Trekker

Joined:

Aug 29, 2016

I have a column formula that I need to re-use frequently. The purpose of the formula is to fil in one or more consecutive missing cell values with the value above. Here's the formula:

 

While( Is Missing( Lag( :Column 1, i ) ) & Row() - i > 1, i++ );
Lag( :Column 1, i );

 

(The second clause in the While condition prevents a infinite loop when Row 1 IsMissing.) And here is what the output looks like when the formula is in Column 2:

I use this formula so often, I thought I might make a user function out of it and attach the script to the table in the "On Open" script. Here's my attempt at making a user function in a user namespace:

Names Default To Here( 1 );
util = New Namespace(
	"util",
	{
		//This function fills in missing values from the value above.
		Infill = Function( {col},
			{Default Local},
			i = 0;
			While( And( Is Missing( Lag( col, i ) ), Row() - i > 1 ), i++ );
			Return( Lag( col, i ) ) ;
		) ; 
	}
);

I attach this script to the table and run it, and then I replace the explicit formula in column 2 with 

util:Infill(Column 1)

But this doesn't work. The output looks like this:

It looks like the While loop is never executing.

Can you help me with what is wrong?

2 REPLIES
john_madden

Community Trekker

Joined:

Aug 29, 2016

P.S. in the column formula version, the variable i is created as a Local Variable and initialized to 0.
stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

For Each Row() can replace your entire loop code. Also see https://community.jmp.com/t5/JMP-Scripts/Fill-in-Missing-Cells/ta-p/21700 for an add-in that does this already plus it has some comments you might find helpful.