topic Re: Keeping duplicate columns in Discussions

Keeping duplicate columns

adam — Fri, 03 Nov 2017 09:18:27 GMT

Hi,

I have 2 data table and these table are similar to certain extent where the test parameters(which means perhaps 30% of the columns sharing the same name) are same as they are different by test location hence the test result will be different as well. I intend to combine this table using Update and at the same time maintaining the 2nd table columns data but automatic rename those columns so that I can view them side by side. Can this done via scripts.

Thanks.

Re: Keeping duplicate columns

ih — Fri, 03 Nov 2017 11:47:52 GMT

I am not sure exactly what you want to do, but yes you can rename columns using a script. If you open each data table and keep separate references to each, you can send messages to all of them to rename columns:

Names default to here( 1 );

//Open two tables
dt1 = Open("$SAMPLE_DATA/Iris.jmp"); //first table
dt1 << Set Name( "Table 1" );
dt2 = Open("$SAMPLE_DATA/Iris.jmp"); //second table (different filename)
dt2 << Set Name( "Table 2" );

//Concatenate tables
dtJoined = dt1 << Concatenate( dt2 );
dtJoined << Set Name( "Combined" );

//Change column names
Column( dt1, "Sepal length" ) << Set Name( "Sepal_length" );
Column( dt2, "Sepal length" ) << Set Name( "Sepal_length" );
Column( dtJoined, "Sepal length" ) << Set Name( "Sepal_length" );

If changing a lot of column names you could use a function to replace the last three lines:

//Make a function to change column names in all tables
ChangeAllNames = function({original, new},
	Column( dt1, original ) << Set Name( new );
	Column( dt2, original ) << Set Name( new );
	Column( dtJoined, original ) << Set Name( new );
);

//Then call the function
ChangeAllNames( "Sepal width", "Sepal_width" );
ChangeAllNames( "Petal length", "Petal_Length" );

Re: Keeping duplicate columns

adam — Mon, 06 Nov 2017 02:53:24 GMT

Thanks ih. Though this is not exactly what I want but the idea is there. Attached file is something that I wanna do.Joining 2 data table and auto renamed 2nd data table columns that matched the 1st data table. Sorry, if there is any confusion from my initial message.

Re: Keeping duplicate columns

txnelson — Mon, 06 Nov 2017 03:18:14 GMT

What you are asking for is the default action taken in JMP when you Join 2 data tables. If they have columns of the same name in both data tables, JMP renames the columns. Here is a script version, however, if you have 2 tables, you can interactively do this by

Tables==>Join

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA\big class.jmp" );
dt2 = Open( "$SAMPLE_DATA\big class Families.jmp" );

Data Table( "big class Families" ) << Join( With( Data Table( "big class" ) ), 
	By Row Number );

Re: Keeping duplicate columns

ih — Mon, 06 Nov 2017 03:29:34 GMT

To expand on Jim's point you will need to join by row as you do not appear to have a key pair as in a typical join. I am having a hard time understanding why you would join the tables in this way; does SN AX0001 relate to STX0030 but not STX0031? If so maybe there is another column you could add to both tables to join them in a more meaningful way.

Re: Keeping duplicate columns

adam — Mon, 06 Nov 2017 03:58:26 GMT

Thanks ih & Jim. That's right in normal circumstances, with less variables the table can be joined without any issues.