HOW to delete the same data in the table？

[#1]

dear，

 every time i go to menu which “row” to detele  the same data ，

i  want to delete the same data in  the table automaticly， and  i don t know to how  to write JSL  

how i do to write ？

[#2]

Assuming there is some condition you can write to select the data you want to delete, check out Select Where() in the scripting index.

e.g.:

dt = Current Data Table();
dt << Select Where( :Col1 == "A") << Delete Rows;

This would delete any rows in Col1 with the value "A".

[#3]

thanks，

but   if i  refresh data  ， i can not delete the same data automatic which below to 

dt = Current Data Table();
dt << Select Where( :Col1 == "A") << Delete Rows;

[#4]

Please clarify.  I am confused.  In the data table you attached, you have 2 columns, "first" and "second".  If you want to delete the rows based upon finding the value of "A" in the column named "first" the JSL would be:

dt = Current Data Table();

dt << Select Where( :first == "A") << Delete Rows;

If you wanted to delete rows based upon the value of "A" being in the column named "second", the JSL would be:

dt = Current Data Table();

dt << Select Where( :second == "A") << Delete Rows;

So when you say "but   if i  refresh data "........are you indicating, that when you reopen the data table?  Are you expecting the JSL to be automatically re-executed when you open the data table again?  

[#5]

thanks，

the data table  comes from database，and everytime i open it，  the data is refreshed 。

so  i expect the JSL to be automatically re-executed to delete  the  same  data  appear in the table。

---

@txnelson wrote:

Please clarify.  I am confused.  In the data table you attached, you have 2 columns, "first" and "second".  If you want to delete the rows based upon finding the value of "A" in the column named "first" the JSL would be:

dt = Current Data Table();

dt << Select Where( :first == "A") << Delete Rows;

If you wanted to delete rows based upon the value of "A" being in the column named "second", the JSL would be:

dt = Current Data Table();

dt << Select Where( :second == "A") << Delete Rows;

So when you say "but   if i  refresh data "........are you indicating, that when you reopen the data table?  Are you expecting the JSL to be automatically re-executed when you open the data table again?  

---

data  when i open the data table again。

[#6]

When you open the data from the database, you will have to add the JSL to the script that you use for retrieving the data.  What method are you using for getting the data from the database?

[#7]

thanks

I use ”Built Query“of JMP menu to get data from database，  

---

@txnelson wrote:

When you open the data from the database, you will have to add the JSL to the script that you use for retrieving the data.  What method are you using for getting the data from the database?

---

[#8]

The JMP Query Builder lets you add JSL to the query.  Look at the documentation on Page 121 in

     Help==>Books==>Using JMP

It will allow you to add to your query the code to automatically delete the desired rows, every time you extract the data.

[#9]

thanks

attached file with excel "Solubility" is original data which i want to modify

attached file"data.dsn" is database

the result which i want to be  is  atteched excel named "delete" 

I write “ SELECT distinct [LOT NO],code,[abnormal content] FROM [Solubility$]”

to delete the same data which in "LOT NO" 

but it can not worked .

[#10 · Accepted solution]

Your problem is that you are using DISTINCT across all columns of solubility. Because for LOT NO 1700002, you have unique values (ie 'elec break', 'pin hole') in column abnormal content, you keep getting the same rows back because each row is unique on the set of columns.

If I remember correctly, Excel (ie M$) sql has a FIRST or LAST version, SAS (ie JMP) does not (although that might have changed under current versions.

Here's code I wrote that seems to answer your question:

SELECT t2.*

FROM Solubility  t2

WHERE t2."Column 1" IN ( 

SELECT DISTINCT t1."Column 1"

FROM Solubility  t1 

GROUP BY t1."LOT NO"

HAVING t1."Column 1" = Max(t1."Column 1"));

I opened Solubility into JMP 

I added a column, Column 1 (think of this as a Sort/Order column)

Next I went into SQL Query builder under tables

After Picking Solublity as table, I picked "column 1", and "lot no", which I'm going to use in a coorelated sub-query (ie a temporary table), and checked DISTINCT Rows only, and clicked add

Now I clicked the triangle next to query Name and chose Convert to Sql. This will let me write the code (30 years of SAS - click and point only get you so far).

I editted the code to what I gave you above, ran the query, corrected any errors that came up, until it ran right.

Basically I matched your modified solubility on column 1 on the correlated subquery mentioned above. The subquery returns unique "column 1"s by taking the maximum column 1 of each group of "lot no". I'm using Max() here as a subsitute for last. Min() could be used as a substitute for FIRST.

I could have also done this as a JOIN on the subquery as another table. We'll leave that for tomorrow.

I know this is probably pretty esoteric, but if you have any questions let me know, Ill try to answer them.

RCW