topic Re: Column dialog box - copies column properties too in Discussions

Column dialog box - copies column properties too

luqi — Tue, 23 Feb 2016 16:59:39 GMT

I have a script that opens multiple data tables and creates new columns in those data tables. Is it possible to script a column copier box that shows every columns from every data table opened? so I can select and copy the only columns I want with their properties?

My current code tries to concatenate the data tables into one new table before copying the columns but it looks like JMP would not let me concatenate more than two data tables at the same time.

Below is a summary of the code I wrote to make it easier to edit. Thanks.

Names Default To Here( 1 );

// Use 'PickFile()' to get a list of files to open (Windows only)

fileList = Pick File(

"Select one of more files", // Prompt

"$DESKTOP", // Initial folder

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)

1, // Initial file filter to apply (index of item in the list above)

0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.

Empty(), // Default file

multiple // Multiple - If 'Save Flag' = 0, using a value of 1 allows more than one file to be opened (ignored by OS/X)

);

// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves

If( !Is List( fileList ),

fileList = Eval List( {fileList} )

);

// Open the files and store their corresponding JMP table names

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dt = Open( fileList[f] );

Insert Into( tableNames, dt << getName );

);

dt = New Table("Concatenated Dog H");

//Create Column Copier to copy files

dt = Current Data Table();

col_dlg = new window("Column Copier",

panelbox("Select four columns to create a new table with:",

col_clist = collistbox(all, width(200), max selected(4)),

buttonbox("OK",

selected_column_list = col_clist << getselected;

col1 = selected_column_list[1];

col2 = selected_column_list[2];

col3 = selected_column_list[3];

col4 = selected_column_list[4];

// Create a new table using just the two selected columns

dt2 = dt << Subset( columns(column(dt, col1), column(dt, col2), column(dt, col3), column(dt, col4) ) );

col_dlg << close window;

)

);

Data table added.

Message was edited by: Luqman Olawin

Re: Column dialog box - copies column properties too

txnelson — Tue, 23 Feb 2016 18:07:21 GMT

In the last posing of this question, you indicated that you did not want to concatenate the tables togetherr, but wanted to join them hoizontally. Below is a rework of the script that I previously sent you, but this time it has the tables being joined horizontally.

Names Default To Here( 1 );

// Use 'PickFile()' to get a list of files to open (Windows only)

fileList = Pick File(

"Select one of more files", // Prompt

"$DESKTOP", // Initial folder

{"JMP
Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)

1, // Initial file filter to apply (index of item in the list above)

0, // Save flag -Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.

Empty(), // Default file

multiple// Multiple - allowsmore than one file to be opened (ignored by OS/X)

);

// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves

If( !Is List( fileList ),

fileList= Eval List( {fileList} )

);

// Open the files and store their corresponding JMP table names

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dttemp= Open( fileList );

Insert Into( tableNames, dtemp << getName );

// Append the data tables together

If( f == 1,

dt= dttemp,

dtx= dt << Join( With( dttemp ), By Row Number );

Close( dttemp, nosave );

dt= dtx;

Close( dtx, nosave );

);

//Create Column Copier to copy files

col_dlg = New Window( "Column Copier",

Panel Box( "Select four
columns to create a new table with:", col_clist = Col List Box( all, width( 200 ), max selected( 4 ) ), ),

Button Box( "OK",

selected_column_list= col_clist << getselected;

col1=selected_column_list[1];

col2=selected_column_list[2];

col3=selected_column_list[3];

col4=selected_column_list[4];

// Create a new table using just the two selected columns

dt2= dt << Subset( columns( Column( dt, col1 ), Column( dt, col2 ), Column( dt, col3 ), Column( dt, col4 ) ) );

col_dlg<< close window;

)

);

Re: Column dialog box - copies column properties too

luqi — Tue, 23 Feb 2016 19:33:28 GMT

Hello Jim,

Thanks for the help, I figured that out after several tries. I should have replied back that thread. The below was the code I used. The problem I have now is that I cannot join more that 2 tables together. Every time I try to join 3 tables, it shows out 2 different concatenated tables instead of one. The first one shows concatenated data table 1 and 2 and the second shows concatenated data table 1 and 3.

Without one concatenated data table, it's very hard to use the column copier. So I was thinking instead, is it possible to use the column copier so it shows all columns from multiple data tables?

Names Default To Here( 1 );

// Use 'PickFile()' to get a list of files to open (Windows only)

fileList = Pick File(

"Select one of more files", // Prompt

"$DESKTOP", // Initial folder

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)

1, // Initial file filter to apply (index of item in the list above)

0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.

Empty(), // Default file

multiple// Multiple - allows more than one file to be opened (ignored by OS/X)

);

// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves

If( !Is List( fileList ),

fileList= Eval List( {fileList} )

);

// Open the files and store their corresponding JMP table names

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dttemp= Open( fileList[f] );

Insert Into( tableNames, dttemp << getName );

// Append the data tables together

If( f == 1,

dt= dttemp,

dtx = dt<< Join ( With ( dttemp ), By Row Number, By Matching Columns (name=name),Preserve Main Table Order(), Output table name("Concatenated Watch H") );

Close( dttemp, nosave );

dt = dtx;

);

dt = Current Data Table();

//Create Column Copier to copy files

col_dlg = New Window( "Column Copier",

Panel Box( "Select four columns to create a new table with:",

col_clist = Col List Box( all, width( 200 ), max selected( 4 ) ),

Button Box( "OK",

selected_column_list= col_clist << getselected;

col1= selected_column_list[1];

col2= selected_column_list[2];

col3= selected_column_list[3];

col4= selected_column_list[4];

// Create a new table using just the two selected columns

dt2= dt << Subset( columns( Column( dt, col1 ), Column( dt, col2 ), Column( dt, col3 ), Column( dt, col4 ) ) );

col_dlg<< close window;

)

);

Re: Column dialog box - copies column properties too

txnelson — Tue, 23 Feb 2016 20:57:08 GMT

The code is intended to open the first data table, then open the second table, create a new joined data table, close the original table and the table just opened, keeping the newly joined table. Then open the third table. Join it to the previously joined table, creating a new data table. Then close the olded joined table, and the newly opend table, keeping the latest joined table.....etc.

There may be a timing problem, and there is an issue that you removed one of the close functions. Please change to the following code:

If( f == 1,

dt= dttemp,

dtx = dt<< Join ( With ( dttemp ), By Matching Columns (name=name),Preserve Main Table Order(), Output table name("Concatenated Watch H") );

wait(1);

Close( dttemp, nosave );

close (dt,nosave);

dt = dtx;

);

Re: Column dialog box - copies column properties too

luqi — Fri, 26 Feb 2016 00:55:24 GMT

Thanks Jim. The timing did not solve the issue ( I also tried increasing it). However, the close function works good. Only problem now is that the 3rd file opens by it self without joining with the latest joined table. I have been trying different methods since yesterday but it doesn't work.

Re: Column dialog box - copies column properties too

txnelson — Fri, 26 Feb 2016 04:00:45 GMT

The issue is that JMP renames the columns when it joins tables, when the tables have columns with the same name. Thus, your column called "name" was getting changed after the first join. So change the line:

dtx = dt<< Join ( With ( dttemp ), By Matching Columns (name=name),Preserve Main Table Order(), Output table name("Concatenated Watch H") );

dtx = dt<< Join ( With ( dttemp ), By Matching Columns (name=name),Preserve Main Table Order(), Output table name("Concatenated Watch H"), merge same name columns(1) );

Re: Column dialog box - copies column properties too

luqi — Fri, 26 Feb 2016 07:31:05 GMT

Doesn't work either. The change does not even join the tables.

Re: Column dialog box - copies column properties too

txnelson — Fri, 26 Feb 2016 13:45:40 GMT

Tell me about the different files. Do they have columns that have the same names? Any columns that have the same names will collapse on top of each other in the join, using the "merge same name columns(1)" statement. If you are counting on the join keeping the same named columns separate, then the section of code that does the joining will have to recognize that the column called "name" in the joined data table will no longer be named "name", but rather "name of <data table 1 name>" and that will have to be handled in the script to allow the joining to happen.

Is column "name" in your data tables a unique identifier for each row?

Can you tell me what error messages are being displayed in the JMP Log (View==>Log)?

Here is the code that works without error on my system. Please note that the directory I am opening to find the input files, and also the column that is used to match on in the joining is different from your code. Other than that, it should be the same code. This code works without error. Please note, the assumption for this code to work is that except for the column used for the matching ("Wafer ID in lot ID").

Names Default To Here( 1 );

fileList = Pick File(

"Select one of more files", // Prompt

"c:\users\txjim\documents\multifiles", // Initial folder

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)

1, // Initial file filter to apply (index of item in the list above)

0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.

Empty(), // Default file

multiple// Multiple - allows more than one file to be opened (ignored by OS/X)

);

If( !Is List( fileList ),

fileList = Eval List( {fileList} )

);

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dttemp = Open( fileList );

Insert Into( tableNames, dttemp << getName );

If( f == 1,

dt = dttemp,

dtx = dt << Join(

With( dttemp ),

By Matching Columns( dt:Name("Wafer ID in lot ID") = dttemp:name("Wafer ID in lot ID") ),

Preserve Main Table Order(),

merge same name columns(1),

Output table name( "Concatenated Watch H" )

);

Close( dttemp, nosave );

Close( dt, nosave );

dt = dtx;

);

dt = Current Data Table();

col_dlg = New Window( "Column Copier",

Panel Box( "Select four columns to create a new table with:", col_clist = Col List Box( all, width( 200 ), max selected( 4 ) ), ),

Button Box( "OK",

selected_column_list = col_clist << getselected;

col1 = selected_column_list[1];

col2 = selected_column_list[2];

col3 = selected_column_list[3];

col4 = selected_column_list[4];

dt2 = dt << Subset( columns( Column( dt, col1 ), Column( dt, col2 ), Column( dt, col3 ), Column( dt, col4 ) ) );

col_dlg << close window;

);

Re: Column dialog box - copies column properties too

luqi — Fri, 26 Feb 2016 17:54:30 GMT

Below is the error I get from the log window. I have edited the post to attach the data tables and they all have the same columns with only different table names. I would like to keep the same named columns separate so when the column copier appears, I can select the columns I like to be copied. Thanks.

Scoped data table access requires a data table column or variable in access or evaluation of 'dt:Wafer ID in lot ID' , dt:Wafer ID in lot ID/*###*/

In the following script, error marked by /*###*/

Names Default To Here( 1 );

fileList = Pick File(

"Select one of more files",

"$DESKTOP",

{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"},

Empty(),

multiple

);

If( !Is List( fileList ),

fileList = Eval List( {fileList} )

);

tableNames = {};

For( f = 1, f <= N Items( fileList ), f++,

dttemp = Open( fileList );

Insert Into( tableNames, dttemp << getName );

If( f == 1,

dt = dttemp,

dtx = dt << Join(

With( dttemp ),

By Matching Columns(

dt:Wafer ID in lot ID/*###*/ = dttemp:Wafer ID in lot ID

Preserve Main Table Order(),

Output table name( "Concatenated Watch H" )

);

Close( dttemp, nosave );

Close( dt, nosave );

dt = dtx;

);

dt = Current Data Table();

col_dlg = New Window( "Column Copier",

Panel Box( "Select four columns to create a new table with:",

col_clist = Col List Box( all, width( 200 ), max selected( 4 ) )

Button Box( "OK",

selected_column_list = col_clist << getselected;

col1 = selected_column_list[1];

col2 = selected_column_list[2];

col3 = selected_column_list[3];

col4 = selected_column_list[4];

dt2 = dt << Subset(

columns(

Column( dt, col1 ),

Column( dt, col2 ),

Column( dt, col3 ),

Column( dt, col4 )

)

);

col_dlg << close window;

)

);

Re: Column dialog box - copies column properties too

txnelson — Fri, 26 Feb 2016 18:03:16 GMT

The error is pointing out that there is no column named "Wafer ID in Lot ID". This is what the matching column in my data tables is. I believe the column name that you are matching on is called "name". Change the 2 references to "Wafer ID in Lot ID" to "name"

Re: Column dialog box - copies column properties too

luqi — Mon, 29 Feb 2016 21:30:07 GMT

Hello Jim,

I tried to replace it with "name". I commented the "By matching column" function and that seems to work for me. Thank you very much for the help, I really appreciate it!