961666_en-US

liliconcom

Count number of unique items in column that meet some criteria

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.

 

 

Data AccessRe: Count number of unique items in column that meet some criteria

The closest built in function that comes to mind is Col Rank( :height, <. 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 )

 

 

Re: Count number of unique items in column that meet some criteria

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]));

 

 

Re: Count number of unique items in column that meet some criteria

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

Re: Count number of unique items in column that meet some criteria

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

Re: Count number of unique items in column that meet some criteria

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.

Re: Count number of unique items in column that meet some criteria

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.

Re: Count number of unique items in column that meet some criteria

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;
);

 

Re: Count number of unique items in column that meet some criteria

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

Re: Count number of unique items in column that meet some criteria

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 

Re: Count number of unique items in column that meet some criteria

Here's a technique for exploring the rabbit hole:

New Table( "Untitled",
	Add Rows( 3 ),
	New Column( "a",
		Formula(Write( "\!nA" || Char( Row() ) );Row();),
		Set Selected
	),
	New Column( "b",
		Formula(Write( "\!nB" || Char( Row() ) );:c + :d + 100;)
	),
	New Column( "c",
		Formula(Write( "\!nC" || Char( Row() ) );:a + 1000;)
	),
	New Column( "d",
		Formula(Write( "\!nD" || Char( Row() ) );:a + 10000;)
	),
	new column( "e",
		formula(write("\!nE" || char(row())); asconstant(write("\!nE as constant "|| char(row())); Q=a+b+c+d); Q;)
	)
);

/*

A1 << A must be done first, every other column depends on A
A2
A3
D1
D2
D3
C1
C2
C3
B1 << B must be after C and D
B2
B3
E as constant 1 << the constant is calculated before the first cell, but using row 1
E1 << E must be last; the asconstant value uses all other columns
E2
E3

*/

The ; is a JSL operator that executes a series of statements and returns the last statement's value. That means a print/show/write statement can be prepended to a calculation to make a trace in the log.

Above I learned the asconstant(...) is pulled out before the first row is calculated, but with row 1 as the current row. 

JMP also sorts through the columns to figure out which ones can evaluate first.

 

Re: Count number of unique items in column that meet some criteria

Column E would make more sense like this:

	New Column( "e",
		formula( 
			Write( "\!nE" || Char( Row() ) ); 
			As Constant( Write( "\!nE as constant " || Char( Row() ) ); a + b + c + d; ); 
		)
	)

The Q variable is not needed and hides AsColumn's purpose in life.

Re: Count number of unique items in column that meet some criteria

Here is one more link which can be sometimes helpful (don't force everything into single formula) Using JMP > Create Formulas in JMP > Create Formulas in JMP 

Additional benefit is that if you have multiple formulas, is much easier to verify the results and in some cases debug the formulas.