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

[#1]

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

[#2 · Accepted 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]

Thanks. This options looks like it will work.

[#4]

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