topic Re: Counting number of values or words in each column in Discussions

Counting number of values or words in each column

jearls11 — Tue, 13 Feb 2024 12:19:58 GMT

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

Re: Counting number of values or words in each column

dlehman1 — Tue, 13 Feb 2024 13:12:38 GMT

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.

Re: Counting number of values or words in each column

mmarchandTSI — Tue, 13 Feb 2024 13:21:39 GMT

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.

Re: Counting number of values or words in each column

txnelson — Tue, 13 Feb 2024 13:34:04 GMT

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.