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.
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.
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.
ah...you are correct about the Include the non matches from the base table....my error
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.
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()));