273421_en-US

liliconcom

Clearing data in selection

I have a big table with several rows and columns. I want to delete some data from the table.

For e.g delete data of row 2 upto column no. 10  based on some criteria. But, keep data in the same row from column 11 intact. The code I have is as follows:

cdt = Current Data Table();
cdt << select columns( 1 :: 21 );
cdt << select where(Is Missing(:Name("Record no.")));

I want to delete the data that's been selected.

There must be a simple way of doing this, but I really don't know how to do it. I can right click and select clear.

But, I want a jsl script code so that this task can be automated.

Automation and ScriptingConsumer and Market ResearchRe: Clearing data in selection

Thank you very much. I really appreciate the great length you have gone to help me solve the problem & understand what JMP really is. People keep on saying JMP isn't excel, but it is very hard to keep that on mind when you are moving from excel to JMP. Also, jsl makes one think JMP to have capability like that of MATLAB or Python which may not always be true..

I couldn't agree less regarding the need of training & educating oneself on JMP.

Like me, majority of us who are here are people who started using JMP once they started working in the industry.

It may not be good for learning, but sometimes asking for solutions in forums gives faster results rather than reading through the

documentation(which is the right thing to do). Your solutions in the community thread have been of great help for me so far and I am very thankful for all of it.

Re: Clearing data in selection

There are cases, where going back to a tool that you have spent years with, learning the in's and out's, is the quick/correct thing to do.  And Excel is certainly the primary tool of record when it comes to users new to JMP.

In particular, given that Excel is exCELL based and JMP is column based, JMP will not have functions that deal with things like moving some cells in a given column, up or down in the column.  That is because JMP assume column values in the same row, have a relationship.  Excel does not have such an assumption.  On the other hand, since JMP is Column Based it takes advantage of that in many of the functions, platforms, etc.  In many cases, one do some extra work, and get it to do some of the things that JMP does very easily.  Excel's Vlookup is one of the items that takes much more effort to get data "Looked Up" than JMP's Update Platform.  Conversely,  the task you questioned the community about, moving cells up in a column requires JMP to do more work to get it accomplished.  One thing that is true about JMP, over Excel, is that JMP is more extensive/complex, because of it's analytical purpose.  So it does take more training/education.  It is for that reason, that you will see members of the Community, urging individuals to read the JMP documents.  They are really the only way to really grasp what can be done with JMP, and how to approach it.

Below is a script that moves the non missing rows within each column up.  It is just one of the ways I can envision one doing this.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt1 = dt << subset(selected rows(0), selected columns(0));
dt1 << set name( "Example 1" );

// randomly clear some data from the different columns
// to have different cells blank or with missing values
For( i = 1, i <= N Cols( dt1 ), i++,
 For( k = 1, k <= 5, k++,
  If( Column( dt1, i ) << get datatype == "Character",
   Column( dt1, i )[Random Integer( 1, N Rows( dt1 ) )] = "",
   Column( dt1, i )[Random Integer( 1, N Rows( dt1 ) )] = .
  )
 )
);

// For the example, create a copy of the Example 1 data table to work on, 
// so the beginning and modified tables can be viewed
wk1 = dt1 << subset(selected rows(0), selected columns(0));
wk1 << set name("Working 1");

// Remove the missing cells for each column and move up the remaining
// rows to fill in
For(i=1,i<=n cols(wk1),i++,
 dtTemp=wk1<<SUBSET></SUBSET>

Re: Clearing data in selection

Currently, I don't have any idea of how it can be done. My fix to this problem now is to
export the jmp table into excel.

cdt << Save("C:\Users\Neal\Desktop\Data entry.xlsx");

After exporting to excel, I get rid of the empty cells and put the data back to jmp table.

Re: Clearing data in selection

What have you tried?

Re: Clearing data in selectionThank you for the solution. It works. But, I have another problem now.
After deleting all the cells, I want to consolidate the data. ie, data will shift up to take
space left behind after deleting. Is there a jsl code to achieve that?Re: Clearing data in selection

Here is an alternative way to code this.  It handles a numerical range of columns, and it assumes they are character columns, so it sets their values to a blank "".

names default to here(1);
cdt = open("$SAMPLE_DATA/Big Class.jmp");
rows = cdt << get rows where (:sex=="F");
cdt[rows,4::5]= "";

Re: Clearing data in selection

Your solution would work for numeric columns. But, what about non-numeric ones.

Also, instead of going one column after the other. Isn't it possible to just do a range. Something like 1::4?

Re: Clearing data in selection

OK, sorry, I read your description again:

cdt = open("$SAMPLE_DATA/Big Class.jmp");
rows = cdt << get rows where (:sex=="F");
column(cdt,4)[rows] = .;
column(cdt,5)[rows] = .;

Re: Clearing data in selection

I want to just delete the selected contents. If I use

Delete Rows

it will delete the whole row. As I said, I don't want the whole row deleted.

Re: Clearing data in selection

... or maybe I am being dumb, and you mean that you want to clear the contents of the selected cells?

Re: Clearing data in selection

I think what you are asking for, is that once you have selected certain rows, how do you delete them?  You can use the message delete rows:

dt << delete rows;

If you check the scripting index you will see that only selected rows are deleted!

Re: Clearing data in selection

Your solution is to clear the selection.

I am looking for 'clear'-- delete contents of the selection.

Sorry, I wasn't able to explain clearly.

Re: Clearing data in selection

You can use the messages clear column selection and clear select for columns and rows respectively.

cdt = open("$SAMPLE_DATA/Big Class.jmp");
cdt << select columns(4::5);
cdt << select where(:sex=="F");
wait(2);
cdt << clear column selection; 
cdt << clear select;