Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
abdul_saqib
Level III

Is there a refresh option to update data table in JMP 13.2.1

Hi,

 

I imported some data from Excel into a data table in JMP 13.2.1. Now, some of the cells have been updated in the Excel file and I wonder if it is possible to import the data from the Excel file which now contains updated cells (something like refereshing the data in the JMP data table). Note that the number of rows or columns nor the column headings have been changed; just the numbers are different.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Byron_JMP
Staff

Re: Is there a refresh option to update data table in JMP 13.2.1

I would take Phil's advice but go in a slightly different direction.

 

Lets say you already have the first table (dt1) all formatted and set up.

The you can use the Source script that pulls a new "fresh" copy of the raw excel table (dt2)

Now update table 1 with table 2.  (tables menu, Update)  or use a script like this.

note: you can use multiple columns to define a uniqe identifier for each row.

 

 

dt1 << Update(
	With(dt2 ),
	Match Columns( :Unique = :Unique )
);
JMP Systems Engineer, Pharm and BioPharm Sciences

View solution in original post

10 REPLIES 10
Highlighted
phil_kay
Staff

Re: Is there a refresh option to update data table in JMP 13.2.1

Hi,
If you open the Excel file from JMP using the Excel Import Wizard you get a source script in the data table. Try that. It might help.
Regards,
Phil
Highlighted
abdul_saqib
Level III

Re: Is there a refresh option to update data table in JMP 13.2.1

I right clicked on the 'Source' in the JMP data table and chose 'Run', it created a new data table which did not have the settings I had created in the first data table (row colours by column and data type etc). 

Highlighted
phil_kay
Staff

Re: Is there a refresh option to update data table in JMP 13.2.1

Yes, I would expect that. The source script repeats the import into a JMP table.

 

I think you would have to script the further data pre-process steps. E.g:

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
Wait( 2 );
dt:Age << Data Type( "Character" );
dt:Height << Data Type( Numeric, 2 );

 Also:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Color or Mark by Column( :Age );
Highlighted
Byron_JMP
Staff

Re: Is there a refresh option to update data table in JMP 13.2.1

I would take Phil's advice but go in a slightly different direction.

 

Lets say you already have the first table (dt1) all formatted and set up.

The you can use the Source script that pulls a new "fresh" copy of the raw excel table (dt2)

Now update table 1 with table 2.  (tables menu, Update)  or use a script like this.

note: you can use multiple columns to define a uniqe identifier for each row.

 

 

dt1 << Update(
	With(dt2 ),
	Match Columns( :Unique = :Unique )
);
JMP Systems Engineer, Pharm and BioPharm Sciences

View solution in original post

Highlighted
abdul_saqib
Level III

Re: Is there a refresh option to update data table in JMP 13.2.1

Thanks Byron. Table > Update menu sounds promising. When I clicked on the Table > Update menu in the dt to be updated. It opened the 'Update' dialogue box, however in the 'Update dt.. with data from' section of the dialogue box, JMP showed some file (data tables?) names which did not have relevance to the current dt (not sure how/why JMP chose those files to be shown). I couldn't find a way to locate the updated Excel file to pull the updated data from. Any idea how to choose the updated Excel file to pull the data from, please? Thanks

Highlighted
Byron_JMP
Staff

Re: Is there a refresh option to update data table in JMP 13.2.1

OK, so when you click Tables/Update, you see a list of all the current tables that are open.

Update is essentially an inner join. 

 

What ever is the current active table is the table that you can update with a selected table.

 

If no other tables are open. 

Next, I open the excel table in JMP, add scripts, color the rows, and do anything except change the column names. 

This is my main table. 

Second, I open the new version of the excel table. Outside of JMP this table has been edited or changed or updated somehow.

 

To do the Tables/Update operation:

Select the first table (remember there are only two tables open at this point, the main table and the new copy)

Tables/update.

Select the new copy in the dialog.

Pick the columns to match.

Click OK.

 

Now the data from the new table will over write the data in the Main table.

 

The new copy does not need to be a complete table. It might only contain a few rows, or only a few columns. Either way it updates to the Main table.

 

 

 

JMP Systems Engineer, Pharm and BioPharm Sciences
Highlighted
abdul_saqib
Level III

Re: Is there a refresh option to update data table in JMP 13.2.1

Thanks for the detailed reply Byron.

 

The problem is that I cannot see the Excel file listed in the Update Table dialogue box, even when only the JMP dt and the Excel (updated) file are open. JMP still shows 3 file names with no relevance + they are not opened on the computer right now (please see the enclosed screenshot). Thanks

 

Updated Table.JPG

Highlighted
phil_kay
Staff

Re: Is there a refresh option to update data table in JMP 13.2.1

Hi,
It is not clear to me if you have the Excel file open as a JMP table. It will need to be open as a JMP table for you to select it in the Update Table dialogue.
Regards,
Phil
Highlighted
abdul_saqib
Level III

Re: Is there a refresh option to update data table in JMP 13.2.1

Thanks Phil. It works now. Previously, I missed the point that the Excel sheet needs to be opened as a JMP dt.

Article Labels

    There are no labels assigned to this post.