topic Re: HOW to detele the same data in the table？ in Discussions

HOW to delete the same data in the table？

TONYMA — Thu, 15 Feb 2018 18:38:54 GMT

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 ？

Re: HOW to detele the same data in the table？

cwillden — Thu, 15 Feb 2018 15:47:40 GMT

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".

Re: HOW to detele the same data in the table？

TONYMA — Thu, 15 Feb 2018 18:39:30 GMT

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;

Re: HOW to detele the same data in the table？

txnelson — Thu, 15 Feb 2018 15:27:51 GMT

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?

Re: HOW to detele the same data in the table？

TONYMA — Fri, 16 Feb 2018 05:50:08 GMT

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。

Re: HOW to detele the same data in the table？

txnelson — Fri, 16 Feb 2018 10:03:33 GMT

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?

Re: HOW to detele the same data in the table？

TONYMA — Sun, 18 Feb 2018 05:00:06 GMT

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?

Re: HOW to detele the same data in the table？

txnelson — Sun, 18 Feb 2018 05:38:21 GMT

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.

Re: HOW to detele the same data in the table？

TONYMA — Mon, 19 Feb 2018 09:07:00 GMT

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 .

Re: HOW to detele the same data in the table？

rwright — Mon, 19 Feb 2018 22:02:53 GMT

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

Re: HOW to detele the same data in the table？

rwright — Mon, 19 Feb 2018 22:16:21 GMT

Made slight mistake - using Min will return your "delete" table.

Re: HOW to detele the same data in the table？

rwright — Mon, 19 Feb 2018 22:43:56 GMT

I went ahead and redid it by putting subquery as table so you can see difference. Sometimes one way works better or is clearer than the other. Here the "t1" and "t2" are aliases.

SELECT t1.*

FROM Solubility t1,

( SELECT DISTINCT "LOT NO", Min("Column 1") AS "Column 1" FROM Solubility GROUP BY "LOT NO" ) AS t2

WHERE t1."Column 1" = t2."Column 1"

;

Re: HOW to detele the same data in the table？

TONYMA — Tue, 20 Feb 2018 00:56:22 GMT

i am sorry to tell you that it can not work

the alarm is circular reference;

SELECT t1.*
FROM [Solubility$] t1,
( SELECT DISTINCT "LOT NO", Min("Column 1") as "Column 1" FROM [Solubility$] GROUP BY "LOT NO" ) AS t2
WHERE t1."Column 1" = t2."Column 1"

Re: HOW to detele the same data in the table？

rwright — Tue, 20 Feb 2018 01:46:10 GMT

I just started from scratch and it worked on my end. Tried both MIn() and Max() and it gave what I was expecting.

I did notice a couple of things:

1. When I told you I added column 1, I forgot to mentioned I initialized it to sequential numeric. IE I basically replicated the row number.

2. Noticed you use the brackets around the table name, and the table name ends in "$". Those are MS take on SQL. Try eliminating those.

3. Can't remember if I told you to hit the update button in the custom sql window or not, but seems to help me before I run query.