Count number of unique items in column that meet some criteria

[#1]

Suppose in "Big Class" I'd like a formula that counts the number of unique names of people at or less than the height of the person in the current row.  How can I do that?

I have this:

thisHeight = :height;
Col N Unique( :name, :height <= thisHeight );

I realize the second parameter is the grouping parameter, and it's more intuitive to try to constrain what gets passed into the first parameter, but I haven't had any luck with anything I've tried to this point.

[#2]

The closest built in function that comes to mind is Col Rank( :height, <<tie( "maximum" ) ). The problem is: this counts the total number of rows at or below thisHeight, not the number of unique names. 

Instead, you could try a custom formula, like this: 

thisHeight = :height; //store the current height value

// Build an associative array to collect unique names at or below thisHeight
aa = Associative Array();
For( i = 1, i <= N Rows(), i++,
    If( Column("height")[i] <= thisHeight, 
        aa[Column("name")[i]] = 1  // This only adds to the Associative Array if this is a new, unique name
    )
);

// The number of Items that have been added to the array = number of unique names
N Items( aa )

[#3]

I tried something very close to this yesterday evening and it froze JMP.  I left it to run all night, and when I got in this morning the JMP data table window was blank, unresponsive, and Task Manager says the machine was only using one core and had 40+GB RAM free (so it didn't run out of RAM or take over the CPUs).  My table has nearly 400k rows, so I think this is maybe an O(N^2) kind of solution...

[#4]

Not sure if you can do it with Col functions as the new ones behave a bit differently than old ones.

As Constant(n = :name << get values);
curheight = :height;
r = Where(:height <= curheight);
N Items(Set Unique(n[r]));

[#5]

@jthi   Re. my comment above, this also looks like a O(N^2) solution.  (Really sucks that a bad formula can block/crash the entire session, lost a chunk of my afternoon's work yesterday due to attempting a similar solution.)

Sad that the new Col functions don't work the same, my question was perhaps overly simplistic, I need the grouping columns as well and I'm not sure how to implement them in this solution.  I'll probably just have to do this in python, which is sad because the column won't auto-update and python scripts can't be saved in the table yet.

ps.  In my goal to learn a little bit from you at every interaction ;) -- why does this approach need the "As Constant()" function?  What would happen without it?

[#6]

If you need a formula, I'm not sure how well Python would work. As Constant() is there to prevent getting the list of names again and again on each row.

[#7]

The python solution worked, and was pretty quick to implement/run, all things considered.  

Still hoping that Col N Unique() gets reworked for a future version to be similar to the others.

I also kind-of wish there was a "JMP Formula Cookbook" somewhere that we could leverage/search.  I've actively been coding VBA, AppleScript, perl, python, and javascript (and little bits of C++) for all of my 33 years of my adult life, and JSL is by far the most difficult one of all of them to learn and understand.  I still don't understand why As Constant() would be necessary or what it even does.  I've read most of the Scripting Guide book, but it feels like there needs to be another one somehow.

[#8]

You don't want to evaluate values which won't change on each row of the formula execution. That is what As Constant() does, it makes JMP to evaluate that expression only once, on the first row.

As Constant(expr) 

I think you could basically replace it with (most likely not exactly the same, but quite close)

If(Row() == 1,
	n = :name << get values;
);

[#9]

At risk of taking this question down a rabbit hole, one more question --

Do column formulas effectively get evaluated once, but with each expression evaluated in an array context?

Or does the entire formula get re-executed once per row with Row() dictating which row is currently being evaluated?

Or am I misunderstanding something more fundamental?  My mental model of what JMP is doing with formulas is bifurcated into incompatible pieces...

[#10]

Someone who knows the internals of JMP can give better answer than my guess (@Craige_Hales maybe). I consider them expressions (they are just JSL) which gets evaluated for each row, directly to the datatable, when needed. The when needed is determined by JMP unless I suppress the evaluation. There are some optimizations such as Col functions which evaluate (most of the time) only once. 

There are some useful links from JMP Help but I can quickly only find this one:

• Scripting Guide > Data Tables > Calculator Functions