BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Ges

Community Trekker

Joined:

Mar 11, 2018

How to delete rows based upon a condition

how to make a formula to delete certain rows in column. tasked with deleting all unknowns in the column as well as mixtures of the cultivars. The issue is that the rows are words and a formula in jmp must be used to get full credit for this exercise. attached is a screenshot of the jmp file with the cultivars column. must delete the unknowns and the mixture of cultivars. 

 Picture52999.png

1 ACCEPTED SOLUTION

Accepted Solutions
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

Re: Delete rows based upon condition

@Ges
     My suggestion is that you try and understand the concept . Let us consider the following example: 

// Open a Sample Data Table 
dt = Open( "$SAMPLE_DATA/Cities.jmp" );

// Select Rows which satisfy a condition 
dt << Select Where(:State == "NY");

// Delete Rows 
dt << Delete Rows; 

Now, let us look at the problem you are trying to solve: 

1. You need to select rows that satifies 2 conditions 

    a. Condition 1 - Word Unknown I am presuming in column "Cultivars"

    b. Condition 2 - greater than 2 words per row ? This I am not sure of - are you referring to the same column ? 

 

In order to find rows, which contain a specific word , look at the function called "Contains" in Scripting Index. 

Help ==> Scripting Index ==> Contains 

In order to find rows in a given columns that has more than two words, If they are always separated by a comma or some form of delimiter, you can use the "Words" function. 
Help ==> Scripting Index ==> Words 

 

Best
Uday
5 REPLIES 5
MikeD_Anderson

Joined:

Nov 21, 2014

Re: Delete rows based upon condition

Use "Select Where()" to locate and select the rows to be deleted.  Then "Delete Rows" to delete the rows selected by the first argument.  There are examples for both in the scripting index under Help in JMP.  

 

M

Ges

Community Trekker

Joined:

Mar 11, 2018

Re: Delete rows based upon condition

in order for us to receive full credit we have to complete a formula, we can't just simply delete the rows. I have no idea where to start as far as the formula is concerned. I understand how to open the formula but as to what get put in is the confusing part. how to delete only rows with the word unknown or with greater than 2 words per row. 

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Solution

Re: Delete rows based upon condition

@Ges
     My suggestion is that you try and understand the concept . Let us consider the following example: 

// Open a Sample Data Table 
dt = Open( "$SAMPLE_DATA/Cities.jmp" );

// Select Rows which satisfy a condition 
dt << Select Where(:State == "NY");

// Delete Rows 
dt << Delete Rows; 

Now, let us look at the problem you are trying to solve: 

1. You need to select rows that satifies 2 conditions 

    a. Condition 1 - Word Unknown I am presuming in column "Cultivars"

    b. Condition 2 - greater than 2 words per row ? This I am not sure of - are you referring to the same column ? 

 

In order to find rows, which contain a specific word , look at the function called "Contains" in Scripting Index. 

Help ==> Scripting Index ==> Contains 

In order to find rows in a given columns that has more than two words, If they are always separated by a comma or some form of delimiter, you can use the "Words" function. 
Help ==> Scripting Index ==> Words 

 

Best
Uday
Ges

Community Trekker

Joined:

Mar 11, 2018

Re: Delete rows based upon condition

@uday_guntupalli I am going to upload the file. The file is in excel and we had to copy and paste it as values in excel and then upload from to into jmp. The columns that we are supposed to work with are the Cultivar and mono column. We have been tasked with using a formula to delete specific items in the cultivar column. I have tried this numerous ways and it just deletes the entire column. We have to make a formula to delete the data in which the word unknown appears and also if there are multiple cultivars listed (more than one cultivar per row); ending up with the cultivars that are just one word. I am attaching the excel data in hopes that someone will be able to help me. I have been struggling and have tried to use a formula at least 20 times all with the same outcome of deleting the column. 

They said that the mono column is supposed to help us but I am not sure how because not everything matches meaning that not every uknown has 3 and not every single word has a 1, and not 2 or 3 words have a 2 or a 3. 

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

Re: Delete rows based upon condition

@Ges,
      Are you supposed to use the tab "Data_for_JMP" or "Sheet1". I am assuming the "Data_for_JMP" is the right one. I am going to provide step by step instructions on what to do - but I want you to keep in mind and understand, as a community member I wouldn't be offering a solution knowing it is a homework problem. I will only provide advice so you make a fair attempt at solving it on your own. If you share your work, the community will always be glad to help you, but I will not solve the problem for you as the intent is for you to make an attempt on your own using tools at your disposal. 

 

     Now, let us go over the attachment, assuming "Data_for_JMP" is the sheet with the data you want , 

Step 1: Import the data into JMP

When you open JMP, navigate to File ==> Open . Select the excel file. This should give you the following interface: 

image.png

Step 2: Now you have your raw data in JMP from Excel as a data table, open a new script 
image.png

 

Step 3: Use the example shown below to delete rows that meet your condition 

Clear Log(); Clear Globals(); 

dt = Current Data Table(); 

// Assuming you need to delete the "Twymax" in the Cultivars Column 

// Select Rows where this condition is satisfied 
dt << Select Where(:Cultivars == "Twymax"); 

// Delete Rows 

dt << Delete Rows; 

The above acheives what you need. You will need to edit the script I am providing for your conditions. If you have any further trouble, please share your script or your attempts. 

Best
Uday