Identify lowest 5 sequential rows byVar

BHarris — Tue, 25 Jul 2023 22:19:14 GMT

Suppose I have a table of RunNumber (1,2,3...) and Result (numeric/continuous).

How can I create a formula column that identifies (e.g. with a "1") the sequence of 5 Result numbers with the lowest average value for each RunNumber, and a "0" everywhere else?

Re: Identify lowest 5 sequential rows byVar

txnelson — Tue, 25 Jul 2023 22:58:25 GMT

While putting everything one would need to accomplish what you want into a column formula could be done, it is much simpler using a little script run in open code. Here is an example

names default to here(1);
dt = 
// Open Data Table: semiconductor capability.jmp
// → Data Table( "semiconductor capability" )
Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

// create a column to represent the original order of the data
dt << new column("Orig Row", set each value(row()));

// Find 5 lowest values of NPN1 for each site
dt << sort( by(:NPN1), order(descending), replace table(1));

dt << new column("minimum NPN1", set each value(
	if(col cumulative sum(1,:site) <= 5, 1, 0)
));

// Set the table back to the original order
dt << sort( by(:Orig Row), order(ascending), replace table(1));
dt << delete columns(Orig Row);

topic Identify lowest 5 sequential rows byVar in Discussions

Identify lowest 5 sequential rows byVar

Re: Identify lowest 5 sequential rows byVar