cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar

Conditional Count Across Columns

I have a table with quite a few columns (>100). I am trying to create a column that counts the number of columns in a row that are greater than 1. How would I write a formula to do that? Below is a small picture of an example table.

 

It would be preferable if this was just a formula I could drop into a table and a not a standalone JSL script. 

2 REPLIES 2
ian_jmp
Level X

Re: Conditional Count Across Columns

Here's one way:

NamesDefaultToHere(1);

// Build a table
nr = 20;		// Number of rows
nc = 10;		// Number of columns
dt = AsTable(J(nr, nc, RandomNormal(1, 1)));
dt << setName("Above and Below One");

// Find out what columns are in the table
cols = dt << getColumnNames("String");

// Build an expression to be used as the formula:
// Sum the results of an 'if' clause for each column that exists
form = Expr(Add());
for(c=1, c<=NItems(cols), c++,
	ifClause = Expr(If(columnTBD > 1, 1, 0));
	SubstituteInto(ifClause, Expr(columnTBD), Parse(":"||cols[c]));
	InsertInto(form, NameExpr(ifClause));
);

// Inspect the resulting formula
Print(form);

// Add the formula column
fc = dt << NewColumn("Count Above One", Numeric, Ordinal, Formula(NameExpr(form)));
jthi
Super User

Re: Conditional Count Across Columns

This formula wants your "calculation" column to be the last on the data table and it has to be created before adding the formula. Also this won't get updated like normal formulas, so you will have to open the formula and press Apply:

Sum(Current Data Table()[Row(), Index(1, N Cols(Current Data Table()) - 1)] > 1)
-Jarmo