cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • New to JMP? Join us Sept. 23-24 for the Early User Edition of Discovery Summit, tailor-made for new users. Register now for free!
  • Your voice matters! Tell us how you prefer to receive JMP updates, so we can tailor our communication to your needs. Take short survey.
  • See how to access JMP Marketplace - and - find, create & share add-ins to extend your JMP. Watch video.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
BHarris
Level VII

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.

 

 

11 REPLIES 11
Craige_Hales
Super User

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

*/

Craige_Hales_0-1785844389493.png

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.

 

Craige
Craige_Hales
Super User

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.

Craige

Recommended Articles