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
Yngeinstn
Level IV

Reorder Columns Question

I know that JMP is ordering columns by name in descending order. I have to name my columns how it appears in our test specification to make it easier to identify what test we are referring to. I have 16 channels [0 - 15] and 2 test identifiers [ T & R ]. I concat different data fields in multiple columns. An example is below.  This order makes it hassle to plot and place in a powerpoint because we have to move all the slides around. You can see from the example that it goes 0, 1, 10, 11, 12, 13, 14, 15, 2, 3, 4, 5, 6, 7, 8, 9. Any easy suggestions on how to rectify this. I know i could make it 00, 01, 02,... get it to order correctly but like i mentioned, our spec ommits the leading zero.

 

CH0 R State 3 @ Freq

CH0 R State 4 Delta Amp @ Freq

CH0 T State 1 @ Freq

CH1 R State 3 @ Freq

CH1 R State 4 Delta Amp @ Freq

CH1 T State 1 @ Freq

CH10 R State 3 @ Freq

CH10 R State 4 Delta Amp @ Freq

CH10 T State 1 @ Freq

CH11 R State 3 @ Freq

CH11 R State 4 Delta Amp @ Freq

CH11 T State 1 @ Freq

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Reorder Columns Question

Please mark the last response I sent as a solution.  It really helps other to find the correct solutions in the future.

Jim

View solution in original post

9 REPLIES 9
Highlighted
txnelson
Super User

Re: Reorder Columns Question

I have dealt with this, by having a script that changes the names to the form I need them to be and then I can use the Reorder by Name.  I first create in the script a column property called "Original Name" so that I can always change the name back.  Once the script makes the changes to the names, it can then issue the 

    dt << Reorder by Name

After that, you can reloop through the column names and take the value from the "Original Name" column property, and set the column name back to the original name.

Jim
Highlighted
Yngeinstn
Level IV

Re: Reorder Columns Question

Thanks Jim..
I forgot to mention that I create this column in my SQL script. I try to do as much as i can before the table actually reaches JMP to save time considering i am bringing about 2 million rows of data. So column would be the original column.
Highlighted
txnelson
Super User

Re: Reorder Columns Question

You have me confused. If I understand correctly, you want to reorder the columns alphabetically, but there are non zero filled numbers in the column names. So I think your question is, how to reorder the columns correctly, as if the numbers were zero filled. That Has nothing to do with how many rows there are in the data table. So my suggestion above should still hold. Once you read in your data table, you can create a little script that saves the original names into a new column property, change the column names to have zero filled names, reorder the columns, and then loop again across the columns, changing the names back to the original column names.

This will run real fast, since it doesn't change any data, only the column names.

What am I missing?
Jim
Highlighted
Yngeinstn
Level IV

Re: Reorder Columns Question

I am trying to process what you are describing however i can't figure it out.. Could you please provide an example?

Thanks
Highlighted
txnelson
Super User

Re: Reorder Columns Question

Here is a sample script that for your example data, converts the numbers to 3 digit numbers, reorders the column by name, and then resets the names of the columns back to the original names

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 12 ),
	New Column( "Column 1", Character, "Nominal" ),
	New Column( "CH0 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH0 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH0 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH1 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH1 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH1 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH10 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH10 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH10 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH11 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH11 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH11 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) )
);

wait(5);  // Wait 5 seconds so one can see the original example table

// If a column name starts with "CH" adjust the name
For( i = 1, i <= N Cols( dt ), i++,
	If( Substr( Column( i ) << get name, 1, 2 ) == "CH",
		Column( i ) << set property( "Original Name", Column( i ) << get name );
		tempName = Substr( Column( i ) << get name, 3 );
		number = Word( 1, tempName, " " );
		tempName = Substr( tempName, Length( number ) + 2 );
		number = Substr( "00", Length( number ) ) || number;
		tempName = "CH" || number || " " || tempName;
		Column( i ) << set name( tempName );
	)
);

// Reorder the table names
dt << reorder by name;

// Replace the column names with the original names
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Empty( Column( i ) << get property( "Original Name" ) ) == 0,
		Column( i ) << set name( Column( i ) << get property( "Original Name" ) );
		Column( i ) << delete property( "Original Name" );
	)
);

Jim
Highlighted
Yngeinstn
Level IV

Re: Reorder Columns Question

Thank you Jim for your help... I see what you are doing when you add a leading '0' forcing the 01 - 10 to order properly. The portion of the script that is supposed to be changing the names back to the original's appears to run but the columns don't change. I went into the Column info and the Column Property reads 'column( i ) << Get Name' is this supposed to be that or the actual name? It removes the Column Property but fails to rename the column
Highlighted
txnelson
Super User

Re: Reorder Columns Question

I wrote the script using JMP 15, which properly evaluated the

     Column( i ) << get name

However, after your response, I went back and found that JMP 14 and earlier require an Eval() to force JMP to get the results of the << get name

     Eval( Column( i ) << get name ) 

The code below works for JMP 15 and the previous versions

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 12 ),
	New Column( "Column 1", Character, "Nominal" ),
	New Column( "CH0 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH0 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH0 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH1 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH1 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH1 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH10 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH10 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH10 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH11 R State 3 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH11 R State 4 Delta Amp @ Freq", Numeric, "Continuous", Format( "Best", 10 ) ),
	New Column( "CH11 T State 1 @ Freq", Numeric, "Continuous", Format( "Best", 10 ) )
);

Wait( 5 );  // Wait 5 seconds so one can see the original example table

// If a column name starts with "CH" adjust the name
For( i = 2, i <= N Cols( dt ), i++,
	If( Substr( Column( i ) << get name, 1, 2 ) == "CH",
		Column( i ) << set property( "Original Name", Eval( Column( i ) << get name ) );
		tempName = Substr( Column( i ) << get name, 3 );
		number = Word( 1, tempName, " " );
		tempName = Substr( tempName, Length( number ) + 2 );
		number = Substr( "00", Length( number ) ) || number;
		tempName = "CH" || number || " " || tempName;
		Column( i ) << set name( tempName );
	)
);

// Reorder the table names
dt << reorder by name;

// Replace the column names with the original names
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Empty( Column( i ) << get property( "Original Name" ) ) == 0,
		Column( i ) << set name( Column( i ) << get property( "Original Name" ) );
		Column( i ) << delete property( "Original Name" );
	)
);
Jim
Highlighted
Yngeinstn
Level IV

Re: Reorder Columns Question

Well how fortuitous. Our IT department is in the process of distributing JMP 15 for our company.

Thank you again.
Highlighted
txnelson
Super User

Re: Reorder Columns Question

Please mark the last response I sent as a solution.  It really helps other to find the correct solutions in the future.

Jim

View solution in original post

Article Labels

    There are no labels assigned to this post.