nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Counting number of values or words in each column

jearls11

i have a data set and i want to count the number of "OK" and put the calculate value at the bottom of the row for each column.

For example:

ab	ac	ad
OH;OC;	OK	OK
OH;OC;	OK	OK
OH;OC;	OK	OK
OK	OK	OK
OK	OK	OK
OK	OK	OK
OK	OK	OK
OK	OK	OK
OK	OK	OK
6	9	9

For(i=1,i<=NCol(dt),i++,
Column(dt,i)[NRows(dt)] =  Char(N Rows( dt<< get rows where( As Column(dt,i) == "OK")));
)

I was using the code above but it seems to not let me put the value in for a locked columns.
when i use try() it just ignores it and continues on the script.
The locked columns have formulas in them. is there another way to write this or over write locked column error , i am using jmp 14

Accepted answers

txnelson

Since you are changing the structure of the data table with your addition of a summary value at the bottom of the table, the simplest way to avoid the issue with formula columns is to delete the formula for each column. Formula deletion will not remove cell values, but will just make them static values.

For(i=1,i<=NCol(dt),i++,
try(Column(dt,i)<<delete formula);
Column(dt,i)[NRows(dt)] =  Char(N Rows( dt<< get rows where( As Column(dt,i) == "OK")));
)

The Try() function will just avoid any issue with attempting to delete a formula for columns that do not have a formula.

All comments

dlehman1

There is probably a more direct way to do this, but I think this will work. Create a column of 0,1 values where 1 indicates it is OK. Then use Table Summary to give the total number of 1s. Make that into a data table and then concatenate (after making the column names match) with the original data table.

mmarchandTSI

Remember that JMP is not Excel. Every value in a column will follow the same format, so you can't have a running total at the bottom of your column. For your situation, you could use table variables. Try setting this as a table script:

dt = Current Data Table();
For( i = 1, i <= N Col( dt ), i++,
	dt << Set Table Variable( (Column( dt, i ) << Get Name) || "OK", N Items( dt << Get Rows Where( Column( dt, i )[Row()] == "OK" ) ) )
);

It will have to be run to be up to date, of course, but it will keep your totals visible.

txnelson

For(i=1,i<=NCol(dt),i++,
try(Column(dt,i)<<delete formula);
Column(dt,i)[NRows(dt)] =  Char(N Rows( dt<< get rows where( As Column(dt,i) == "OK")));
)

The Try() function will just avoid any issue with attempting to delete a formula for columns that do not have a formula.