Saving Data Table with Columns Transposed

Question

I have been stuck in trying to get a data table to save correclty after I transposed the columns. I have a data table with spec limits in the first three rows, and I want to save them in a separate file so I can use that file as a limits file that is applied to the raw data as part of a data distribution plot. Each time I open the saved SPEC LIMITS file, it still has the columns in the non transposed format. Both the table with the non-transposed columns and the table with the transposed columns are open on the screen:

dt = Open( path );        // I select the file to data file to open. Spec limits are in first 3 rows

x = 3;                           // Number of rows that are not raw data (and have spec limits)

n = N Row( dt );           // Get total number of rows in the table
For( i = n, i > x, i--,       // Delete all rows starting at row 4 to the end of the table
dt << Delete Rows( i );

n = dt << Get Column Names();
dt << Transpose(
            columns(n),
            Output Table( "SPEC LIMITS" )
        );
:Label << Set Name( "Parameter" );
:Row 1 << Set Name( "LSL" );
:Row 2 << Set Name( "USL" );
:Row 3 << Set Name( "Units" );
dt << Save("SPEC LIMITS.jmp");   // Does not save data table with table columns transposed.

Have also tried saving the table as part of a close instruction and got the same result:
close( dt, Save("SPEC LIMITS.jmp"));

I sure there is an easy explanation, but what is needed to save the table with the columns transposed? Thank you!

JensRiege · Answer

Thank you for the clarification... I will give this a try :)

txnelson · Answer

1. @stephen_pearson code is an excellent improvement over @JensRiege code. However, the source of the problem with the original code, while corrected in @stephen_pearson code, was not annotated in the new code. The main issue with the failure to save the code, was that the variable "dt" was used when saving the data table dt << Save("SPEC LIMITS.jmp"); "dt" points to the original data table, and when the transpose data table was create, dt << Transpose( columns(n), Output Table( "SPEC LIMITS" ) ); apparently an assumption was made, that the variable "dt" would now point to the new data table. @stephen_pearson correctly creates a new column "dt2" to point to the new table created by the "Transpose", and then uses it in the "Save" function dt2 = dt << Transpose( columns(n), Output Table( "SPEC LIMITS" ) ); :Label << Set Name( "Parameter" ); :Row 1 << Set Name( "LSL" ); :Row 2 << Set Name( "USL" ); :Row 3 << Set Name( "Units" ); dt2 << Save("$DESKTOP\SPEC LIMITS.jmp");

Your original code should work, if you added the "dt2" handle to your Transpose(), and changed the "dt" reference on your Save function to "dt2"

stephen_pearson · Answer

Try this:

dt = currentdatatable();        // I select the file to data file to open. Spec limits are in first 3 rows
x = 3;                           // Number of rows that are not raw data (and have spec limits)
dt << select where(Row()>x);
dt << delete rows;

n = dt << Get Column Names("Numeric");
dt2 = dt << Transpose(
            columns(n),
            Output Table( "SPEC LIMITS" )
        );
:Label << Set Name( "Parameter" );
:Row 1 << Set Name( "LSL" );
:Row 2 << Set Name( "USL" );
:Row 3 << Set Name( "Units" );
dt2 << Save("$DESKTOP\SPEC LIMITS.jmp");   // Save data table with table columns transposed.

1) Simplified deleting the rows

2) Only get numeric column names. If there are character columns as well the transpose will fail if Data columns have different data types

3) Specify a location to save the file in ($HOME, $TEMP are also options to explore). If you do not:

I/O problem.
Unable to open in ReadWrite mode.
The system cannot find the file specified.

Unable to save file.