cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
jearls11
Level III

Counting number of values or words in each column

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:

abacad
OH;OC;OKOK
OH;OC;OKOK
OH;OC;OKOK
OKOKOK
OKOKOK
OKOKOK
OKOKOK
OKOKOK
OKOKOK
699
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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Counting number of values or words in each column

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.

 

Jim

View solution in original post

3 REPLIES 3
dlehman1
Level IV

Re: Counting number of values or words in each column

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
Level V

Re: Counting number of values or words in each column

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.

 

mmarchandTSI_1-1707830481469.png

 

txnelson
Super User

Re: Counting number of values or words in each column

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.

 

Jim