Merging JSL setup using "Join"

[#1]

I found I can merge two data sets best by using the "Join" data tables command, this best elminates possible duplicate entries that may exist - column 1 contains unique number for each entry.  I have a script which opens multiple data tables and manipulates the data, but I'm having trouble calling out each data table separately in order to get the "join" script to work correctly:

If I am manually doing this the code is as such:

Data Table( "Untitled 322" ) << Join(Data Table( "Untitled 318" )),

                                  Merge Same Name Columns,

                                  By Matching Columns(:REPLICATE_ID = :REPLICATE_ID ),

                                  Drop multiples(1, 1),

                                  Name("Include non-matches" )(1, 1),

                                  Preserve main table order(1),

                                  Output Table("Merged")

My problem comes in when I try to code this form multiple tables.  The Untitled tables (untitled 322, untitled 318, etc - there may be more depending how many files i open) are designated in my code as .  So I want to somehow take my max and join with every other w.

I set up something as follows:

For (f = Data Table , f >= 1, f--,

     Try(v = w << Join( With(Data Table (f)),

                    *and the rest follows the join table properties listed above*

However my code doesn't work.  Anyone have an idea how I can do this?

[#2]

This might get you started as an example. It's not perfect as after execution the last of the open tables is also closed ...

Close All(data tables);

New Table("a") << Add Rows(3);
:Column 1 << Set Values([1, 2, 3, 4, 5, 6]);
New Column("Label", Character) << Set Each Value("a");
New Table("b") << Add Rows(3);
:Column 1 << Set Values([1, 2, 3, 7, 8, 9]);
New Column("Label", Character) << Set Each Value("b");
New Table("c") << Add Rows(3);
:Column 1 << Set Values([1, 2, 3, 10, 11, 12]);
New Column("Label", Character) << Set Each Value("c");
New Table("d") << Add Rows(3);
:Column 1 << Set Values([1, 2, 3, 13, 14, 15]);
New Column("Label", Character) << Set Each Value("d");

t_count = N Table();

dt_old = Data Table(t_count);
Show(dt_old << get name());

dt_new = Data Table(1);

For(i = 2, i <= t_count, i++,
    dt_old = dt_new;
    dt_new = dt_old << Join(
        With(Data Table(i)),
        Merge Same Name Columns,
        By Matching Columns(:Column 1 = :Column 1),
        Drop multiples(1, 1),
        Name("Include non-matches")(1, 1)
    );
    Close(dt_old);
);

[#3]

Okay, I tried something similar where I sent my open tables to a list:

Before this code, I have code where *txt files are opened (I could have up to 30+ files) and converted into a JMP data table (thus output of up to 30+ tables).  I am then trying to merge all these tables into one table however, some files may contain duplicate information that needs to be deleted out - I found I can do this using the Join table property.  So per the code below.  I successfully get 1 table = MT, and the others are in a list = OTs.  I want to join each OTs from the list and join it to MT so I get:

MT+OT(1) = MergedTable, then MergedTable+OT(2) = MergedTable2, then MergedTable2+OT(3) = MergedTable3, etc until I get one master table containing all the data (and the others are closed)

<code>

openDT = List();

For (f = 1, f <= N Table(), f++,

     Insert Into(open DT, Data Table(f)););

Show(openDT);

MT = DataTable(1);

Close(Data Table(1), nosave);

OTs = List();

For(g = 1, g<= N Items(openDT) -1, g++,

     Insert Into (OTs, DataTable(g));

Close All (DataTables, nosave);

Show(OTs);

Show(MT);

For( h = 1, h <= N Items(OTs), h++,

     MT << Join(With(ST),

          Update,

          By Matching Columns(:Column_1 = :Column_1),

          Drop multiples(1,0),

          Name("Include non-matches")(1, 1),

          Preserve main table order(1),

          Output Table("Merged")););

     Continue());

</code>

When I run the script - everything works fine up until the Join command where I get the following error

"Send Expects Scriptable Object{169} in access or evaluation of 'Send',"

For( h = 1, h <= N Items( OTs ), h++,

MT <<  /*###*/Join(

With(ST),

Update,

By Matching Columns(:Column_1 = :Column_1 ),

Drop multiples(1, 0 ),

Name("Include non-matches" )(1, 1),

Preserve main table order(1 ),

Output Table("Merged" ))/*###*/;

Continue();

Help? I'm banging my head against the wall trying to figure out why this isn't working

[#4 · Accepted solution]

Looks like all tables are closed prior the Join(). The List OTs is just a list of datatable references. They are not "scriptable objects" if the corresponding tables are gone.

Try something like this:

my_path = "/Users/.../Files/"; //path to directory of the.txt files

flist = Files In Directory( my_path ); //List of all files

n = N Items( flist );

// Remove non-text files from flist

For( i = n, i > 0, i--,

  If( Contains( flist[i], ".txt" ) == 0,

  Remove From( flist, i );

  n--;

  )

);

// Function for open text files (edit column and import settings if necessary)

open_file = Function( {x},

  Open(

  my_path || flist[x],

  Import Settings(

  End Of Line( CRLF, CR, LF ),

  End Of Field( Tab ),

  Strip Quotes( 1 ),

  Use Apostrophe as Quotation Mark( 0 ),

  Scan Whole File( 1 ),

  Labels( 1 ),

  Column Names Start( 1 ),

  Data Starts( 2 ),

  Lines To Read( All ),

  Year Rule( "10-90" )

  )

  )

);

// Open first file

MT = open_file( 1 );

//Join all files in flist

For( i = 2, i <= n, i++,

  ST = open_file( i );

  FT = MT;

  MT = FT << Join(

  With( ST ),

  Update,

  By Matching Columns( :Column_1 = :Column_1 ),

  Drop multiples( 1, 0 ),

  Name( "Include non-matches" )(1, 1),

  Preserve main table order( 1 ),

  Output Table( "Merged" )

  );

  // Clean up

  Close( ST, no save );

  Close( FT, no save );

);

[#5]

Hmmm, I can't merge the origianl txt files though - I'm merging the intermediate invisible files created from the split/formatting of the original txt files.  As these files are invisible and intermediate, there is not a root directory where they are saved (the point of why I'm using the invisible function - it saves spaces and time). I get what you're saying though, even though my tables are in  list, they are closed so there's no way to reference them but "opening" them doesn't apply as they are intermediate/invisible files created from the txt file that are not saved is any directory.

Here's the breakdown of what I'm doing:

1. I open the txt files code is similar to what you have (Will have name Data Table ("Result1"))

2. I reformat these txt files (they are not in column format, so I have code where I "split", format, etc, into a more readable data table in JMP. - I will call these INTERMEDIATE tables (Will have name Data Table("Untitled1"))

3. these intermediate files are invisible (there's just too many to make them visible) - I do not save them as I do not want a directory of saved intermediate files that i have to go delete later.

4. My issue is I'm trying to merge the intermediate tables

I see it would be easier to just join the original files, but they do not come in a format to where I'm able to do that, thus my conundrum.

So the code I have (and referenced previously) works if my intermediate tables are not invisible AND I do not close the intermediate tables I'm referencing to join (doesn't work exactly how I want it too, but nonetheless it works) - but then I have ALL the tables open - the merged and unmerged

So the real questions I have are:

a) I have intermediate tables - essentially I want them to be invisible but in order to reference them into the MT/OTs lists, I'm having to make them visible for this to work - essentially I want these invisible though.  So my real question is: How do I reference invisible intermediate tables w/in a loop to recognize each table to be used for further calcualtion (in order to use the join tables fxn it seems I must have it outside the For loop so I can join two instances of the intermediate tables)?

I have attached the script.  maybe this will explain what I'm trying to do.

[#6]

Invisibility is not the problem here. Invisible tables can be referenced to and joined with other invisible or visible tables. Closed and invisible tables are not the same thing. Closed tables are completely useless, they are gone and forgotten. In the script you have placed Close All (Data Tables, nosave); before the merging code part. That should results in that all tables are forgotten about and the merge will fail. Wait until your'e done before closing tables, invisible or not.

Or use my approach above, i.e open a table one at the time, do whatever editing and splitting you want, and then merge them with the current MT before closing and moving on to the next file.

[#7]

I apologize, I guess I'm not being clear:

I understand I need to keep the tables that were added to the list open - if I comment out the Close All(Data Tables, nosave) in my code I get

the following in my Log (I have a reference to an origianl table that shouldn't be in the openDT list, and that transfers into the OTs.  I'm sure I just need a close fxn for that table and I could figure that out.  I've specified previously the merge does work, but not how I want it to work.

But in my code where I have //invisible (for the w = z << Split...), I want to uncomment the invisible, but because the invisible fxn only recognizes the last table of the loop and all others are deleted I need a way to references these and keep them in a list so I can refer to them as a whole. (Close all is also commented out)  How do I do this?

Invisibility is the problem, b/c unless I figure out a way to reference each invisible table then there's no way I can merge multiple tables. It's looking like I'll need to move the Join code into the loop, but I don't know how to incorporate this if it's only looping through one table at a time and I'm needing to reference at least two.