topic Re: Joining / Concatenate table but just include new data in Discussions

Joining / Concatenate table but just include new data

ivan_j_moore — Mon, 29 Aug 2016 08:13:03 GMT

Hi all,

I have scheduled a JMP script to give me a daily data table each day.

What is the best way to Concatenate these tables, but I only want to add new data and not have multiples.

Example Table 1 (Day 1) and then table 7 (Day 7), how do you concatente these tables to have Table 1 and all new data in Table7 included, but not multiples.

Note, Column names will be the same in each table.

Re: Joining / Concatenate table but just include new data

txnelson — Mon, 29 Aug 2016 11:12:35 GMT

Assuming that you have a column or multiple columns that uniquely identify each row, then all you have to do, is to use Join, rather than Concatenate, and to match on the identification column(s). Also make sure that you specify to Merge Same Name Columns and to include non matching records from the "With" table. That will provide you with what you want.

Re: Joining / Concatenate table but just include new data

ivan_j_moore — Mon, 29 Aug 2016 13:42:06 GMT

Hi Jim,

Thanks for the help, again this worked a treat, Only thing I have to include "Non matches" in both tables if I want to make the table include al that data I am looking for, however this makes sense too.

Thnaks.

Re: Joining / Concatenate table but just include new data

ms — Mon, 29 Aug 2016 14:35:19 GMT

Yes, Join is a powerful tool.

In case you already have created a table a lot of multiples there are several ways to identify and delete duplicate.

Below is my (current) favorite jsl snippet for deleting multiple instances. It now compares all columns, but can be tweaked to just consider a limited set of columns.

// Keep only first instance of multiples

dt = Current Data Table(); // Table with potential multiples

instance = Parse("ColMin(Row(), :Name(\!"" || Concat Items(dt << get column names(string), "\!"), :Name(\!"") || "\!"))");

dt << delete rows(dt<<get rows where(instance < Row()));

Re: Joining / Concatenate table but just include new data

txnelson — Mon, 29 Aug 2016 15:47:06 GMT

ah...you are correct about the Include the non matches from the base table....my error

Re: Joining / Concatenate table but just include new data

ivan_j_moore — Mon, 29 Aug 2016 15:53:06 GMT

Hi Jim, the key point for me here was "Merge Same Name Columns" which I hadn't used before.

Thanks for your help much appreciated.