topic Re: How to append legacy data table with new data from database script in Discussions

How to append legacy data table with new data from database script

guyvanhove — Tue, 17 Mar 2020 16:01:52 GMT

Hi all,

I have a static JMP data table that contains old, historical information (legacy data).

I have another JMP data table that contains new data. This table is fed from a database through a JMP database query. That also means JMP automatically provides me with a nice script that allows to update the table from the database each time I run it.

Now I want to have the old table appended with the new data that is being pulled from the database, obviously without losing the old data. Probably JSL is needed for this, but I can't figure it out myself.

Any help gladly appreciated,

Many thanks

Guy

Re: How to append legacy data table with new data from database script

HadleyMyers — Tue, 17 Mar 2020 16:52:09 GMT

Hi Guy,

When running the SQL query (New SQL Query(...)), you can get a reference to the data table by the following method:

1)run the query and save the source script, giving the query a new name: queryname = New SQL Query(...)<<Run;

2) Delete the <<Run part after the query, so you just have New SQL Query (...);

3) Add a line of script below that: queryname<<Run(onruncomplete(dt=queryResult));

4) use the reference to dt with a "Concatenate" function together with the other table.

Please try this and let me know if you can make it work for you.

Re: How to append legacy data table with new data from database script

guyvanhove — Fri, 20 Mar 2020 08:27:53 GMT

Hi Hadley,

I don't understand all of the steps yet.

1) run the query and save the source script, giving the query a new name: queryname = New SQL Query(...)<<Run;

=> with "save the source script", do you mean I just have to EDIT the existing query in my table and save it there? Or add it as a NEW script to my table? Or save this new script elsewhere?

4) use the reference to dt with a "Concatenate" function together with the other table.

=> this needs more clarification I'm afraid

Thanks in advance Hadley!

Guy

Re: How to append legacy data table with new data from database script

HadleyMyers — Fri, 20 Mar 2020 15:13:23 GMT

Hi,

I think we can probably simplify this through the following: we'll call dt1 the table that was saved on our desktop, and dt2 the new table that we want to update from our database. We are going to create a new table script to table dt1 that creates a query for dt2, and then concatenates dt1 and dt2 together, appending the results to dt1.

dt1=Current data table();

queryname=New SQL Query(...
...);

queryname<<Run(onruncomplete(dt2=queryResult));

dt1<<Concatenate(dt2,Append to first table);

To answer your first question, you can get the New SQL Query script from dt2 by running the query and then right-clicking on the source script, saving the result to your clipboard, and then pasting it into a new script window. You can then use the window to work on the script before saving it to a New Table Script in dt1.

Is this helpful?

Re: How to append legacy data table with new data from database script

guyvanhove — Sat, 21 Mar 2020 16:09:08 GMT

Hi Hadley,

I think we are close...

I think I executed everything as you suggested, but when I just open the dt1 and run the script from there, I get this JMP Alert message:

Name Unresolved: dt2 in access or evaluation of 'dt2', dt2/*###*/

Shortly after, the dt2 datatable pops up. But it's not appended to dt1.

Interestingly, when I close this dt2 and try running the script from dt1 again, I no longer get the JMP Alert message, but now the Concatenate window pops up, and shortly after the dt2 pops up again. Still dt2 is not appended to dt1.

Regards

Guy

Re: How to append legacy data table with new data from database script

HadleyMyers — Sat, 21 Mar 2020 16:46:20 GMT

Hi again,

OK, I think I have a solution. As you can see from the script below, I've added the Concatenate function to the inside of the "onruncomplete" message. If this is saved as a table script to dt1, that should work. Please try it and let me know.

dt1=Current Data Table();

query=New SQLQuery (...
);

query<<Run(onruncomplete(dt2=queryResult;dt1<<Concatenate(dt2,Append to first table);));

Re: How to append legacy data table with new data from database script

guyvanhove — Sun, 22 Mar 2020 23:50:35 GMT

Yes, this does the trick, it gets appended. Two remaining issues still:

1) A minor issue is that the dt2 table still pops up separately as well, which is not needed.

I tried to avoid this by changing the script as follows, but the result is the same:

queryname << Run( onruncomplete( dt1 << Concatenate( dt2 = queryResult, Append to first table ) ) );

instead of

queryname << Run(
	onruncomplete(
		dt2 = queryResult;
		dt1 << Concatenate( dt2, Append to first table );
	)
);

2) A bigger issue is that when the script is run a second time, the same data gets appended again at the end of dt1. I need some sort of rule that only imports any new data. Not sure if this can be done in the Concatenate portion of the script.

If there is no workaround for this, as a plan B we may also try to modify the script such that it spits out the appended table (dt1 + dt2) as a seperate table. That way the dt1 can always be used as the basis for future data updates.

Re: How to append legacy data table with new data from database script

HadleyMyers — Sat, 21 Mar 2020 18:40:27 GMT

Hi,

Adding a Close() function to the onruncomplete script will make dt2 open and close so quickly the user won't know it happened:

query<<Run(onruncomplete(dt2=queryResult;dt1<<Concatenate(dt2);Close(dt2,nosave)));

Notice that I also removed the "Append to first table" statement from the concatenate function. This will create a separate table and leave dt1 intact. That would coincide with your suggested workaround (good idea, by the way!). There's probably an easy way of checking each row of both tables and only appending rows that are unique to dt2, but I can't think of it. Maybe someone else has a suggestion?

Re: How to append legacy data table with new data from database script

HadleyMyers — Sun, 22 Mar 2020 23:51:36 GMT

Hi again,

Thinking about it a little more, you could always add a command to remove duplicate rows from dt1 after appending dt2 to dt1. That might resolve the 2nd issue you had.

Re: How to append legacy data table with new data from database script

guyvanhove — Sun, 22 Mar 2020 07:49:47 GMT

Thanks Hadley.

I just added that entire command to the end of my script, changed dt to dt1 (although that shouldn't even matter I think), saved it and ran it. The script still runs without errors or alert messages, but when I run it a second time the duplicates keep on being added.

Re: How to append legacy data table with new data from database script

HadleyMyers — Sun, 22 Mar 2020 11:51:57 GMT

Hi,

I have another solution I think might work. The concatenate function is back to appending to the first table (dt1), but now creates a source column to keep track of which rows were in dt1 and which were appended from dt2. "Onruncomplete" now gets the name of table dt2 and then looks for the ":Source table" column, selecting and deleting the "dt2" rows. It then deletes ":Source table." Please try that and let me know if this works.

query<<Run(onruncomplete(

dt2=queryResult;
dt2name=dt2<<get name();

try(
	dt1<<select where(:Source Table==dt2name)<<delete rows;
	dt1<<Delete Columns(:Source Table);
);

dt1<<Concatenate(dt2, append to first table,create source column);
Close(dt2,nosave))
	
);

Re: How to append legacy data table with new data from database script

guyvanhove — Sun, 22 Mar 2020 22:20:34 GMT

Yes! It works as expected now! Thanks Hadley!

Re: How to append legacy data table with new data from database script

HadleyMyers — Mon, 23 Mar 2020 07:01:15 GMT

Perfect!