Subscribe Bookmark RSS Feed

How do I update from database without overwriting hide and exclude values in datatable.

Zero_Cool

New Contributor

Joined:

May 19, 2017

Hi, brand new to JMP. I set up an ODBC connection to my database and I am able to pull in data. I pull in the last 60 days worth of data. I can go through and exclude or hide data as necessary. When I run the Update from Database script I created all of the rows that I excluded or hid are now back to include and unhid. I have users who will be refreshing and running control charts daily. I don't want them to have to go back and constantly reexclude the same rows everythime they run the charts. Is there an option to keep this information if the data row is the same during import? Maybe only append new data points and now overwrite if it is already in the table?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

Solution

You could do it with a bit of JSL. For it to work you would need to have one column that contained dates (or similar) that were sequential.

  1. Find the max date (or similar) in the current data table.
  2. Run the query with filters where the date was greater than that found in 1. - create as new table
  3. Join the new table to the first (append to orginal)
  4. Delete the table from 2 and save.

You could do this step by step and then use it to replace the original update script in the data table.

3 REPLIES
stephen_pearson

Community Trekker

Joined:

Oct 6, 2014

Solution

You could do it with a bit of JSL. For it to work you would need to have one column that contained dates (or similar) that were sequential.

  1. Find the max date (or similar) in the current data table.
  2. Run the query with filters where the date was greater than that found in 1. - create as new table
  3. Join the new table to the first (append to orginal)
  4. Delete the table from 2 and save.

You could do this step by step and then use it to replace the original update script in the data table.

Zero_Cool

New Contributor

Joined:

May 19, 2017

Thanks. This options looks like it will work.

erichill

Staff

Joined:

Oct 1, 2013

Hey, Zero_cool,

Automatically maintaining row states when updating data would be very hard (impossible?) for use to do reliably, because we have no good way of knowing that the data from the update matches the rows from the original data with new rows at the end.  There could be an ORDER BY in effect, for example, that causes new rows to be mixed in with old rows.

However, if you know for sure in your scenario that updated rows will match with new rows at the end, you could write a script that walks the rows and captures the row state of each row, and then does the database update, and then re-applies the row states.  

 

HTH,

Eric