cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Zero_Cool
Level I

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

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

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

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.

View solution in original post

3 REPLIES 3

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

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
Level I

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

Thanks. This options looks like it will work.

Eric_Hill
Staff

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

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