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

[#1]

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

[#2]

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.

[#3]

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

[#4]

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?

[#5]

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

[#6]

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);));

[#7]

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.

[#8]

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?

[#9]

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. Selecting and removing duplicates row with condition 

[#10]

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.