convert columns to rows

[#1]

data  and desired results

 i would really appreciate your help 

[#2]

The script below is a good start on creating the output table you requested

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);

// Now Find all of the columns that are parameters
parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

// Transpose the parameter columns
dtTrans2 = dt << Transpose(
	columns( Eval( parameterColNamesList ) ),
	Transpose selected rows only( 0 ),
	Output Table( "Transpose 2" )
);

// Add in the new columns to be created
dtTrans2 << Add Multiple Columns( "Column", 5, before first, Character );

// Add the values to the new columns
For( i = 1, i <= N Rows( dtTrans2 ), i++,
	dtTrans2:Column 1[i] = Word( 1, dtTrans2:Label[i], ":" );
	dtTrans2:Column 2[i] = Word( 2, dtTrans2:Label[i], ":" );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = specs["LSL"]
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = specs["USL"]
	);
	If( Column( dt, dtTrans2:Label[i] ) << get property( "Units" ) != "",
		dtTrans2:Column 5[i] = Column( dt, dtTrans2:Label[i] ) << get property( "Units" )
	);
);

// Get rid of the label column which is no longer needed
dtTrans2 << delete columns( :Label );

// Change the names of the Trans2 table to match the names in the Trans1 table
For( i = 6, i <= N Cols( dtTrans2 ), i++,
	Column( dtTrans2, i ) << set name( "Column " || Char( i ) )
);

// Combine the 2 Transformed data tables
dtFinal = dtTrans1 << concatenate( dtTrans2 );

// Cleanup the no longer needed data tables
close( dtTrans1, nosave );
close( dtTrans2, nosave );

[#3]

Thank You! this seems to work just fine. But for some reason  the test name Lo Limit and Hi limits columns are put last. any way we can rearranged them  and label them in the script ?

[#4]

What version of JMP are you using?

[#5]

im using jmp 14

[#6]

Here is a corrected version of the script, that positions the spec columns, etc. at the correct location and also adds in the column labels

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);


// Now Find all of the columns that are parameters
parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

// Transpose the parameter columns
dtTrans2 = dt << Transpose(
	columns( Eval( parameterColNamesList ) ),
	Transpose selected rows only( 0 ),
	Output Table( "Transpose 2" )
);

// Add in the new columns to be created
dtTrans2 << Add Multiple Columns( "Column", 5, before first, Character );

// Add the columns 2-5 in the trans data table
dtTrans1 << Add Multiple Columns( "xColumn", 4,after(:Column 1), Character );
for(i=2,i<=5,i++,
	column(dtTrans1,i)<<set name("Column " || char(i));
);

// Add a row at the end of the table, and add the labels 
dtTrans1 << add rows(1);
dtTrans1:Column 1[6] = "# Test Number";
dtTrans1:Column 2[6] = "Test Name";
dtTrans1:Column 3[6] = "Lo Limit";
dtTrans1:Column 4[6] = "Hi Limit";
dtTrans1:Column 5[6] = "Unit";

// Add the values to the new columns
For( i = 1, i <= N Rows( dtTrans2 ), i++,
	dtTrans2:Column 1[i] = Word( 1, dtTrans2:Label[i], ":" );
	dtTrans2:Column 2[i] = Word( 2, dtTrans2:Label[i], ":" );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = specs["LSL"]
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = specs["USL"]
	);
	If( Column( dt, dtTrans2:Label[i] ) << get property( "Units" ) != "",
		dtTrans2:Column 5[i] = Column( dt, dtTrans2:Label[i] ) << get property( "Units" )
	);
);

// Get rid of the label column which is no longer needed
dtTrans2 << delete columns( :Label );

// Change the names of the Trans2 table to match the names in the Trans1 table
For( i = 6, i <= N Cols( dtTrans2 ), i++,
	Column( dtTrans2, i ) << set name( "Column " || Char( i ) )
);

// Combine the 2 Transformed data tables
dtFinal = dtTrans1 << concatenate( dtTrans2 );

// Cleanup the no longer needed data tables
close( dtTrans1, nosave );
close( dtTrans2, nosave );

[#7]

thanks Jim! the format is perfect. however it doesnt work on with other similar dataset  for some reason. any reason why?

[#8]

Are there any error messages in the log?

What are the differences between the 2 data tables?

Is the script stopping before it's completion?

[#9]

there is no log error. when i run the script, it just doesnt do anything. the only difference is the test names... they are different. but all others headers are the same

[#10]

Try running the code for line 1 through line 26.  All you have to do, is to select(highlight) those rows in your script, and then click on the Run Icon.

Names Default To Here( 1 );
dt = Current Data Table();

// Transpose the Demographics
dtTrans1 = dt << Transpose(
	columns( :SBIN, :HBIN, :Alarms, :Device, :Site ),
	Transpose selected rows only( 1 ),
	Output Table( "Transpose 1" )
);

// Change the name of the first column
dtTrans1:Label << set name( "Column 1" ) << Set Display Width( 150 );

// Change the new column names
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i ) << set name( "Column " || Char( i + 4 ) ) << Set Display Width( 100 )
);

// Change the detail name of the Alarms
dtTrans1:Column 1[(dtTrans1 << get rows where( dtTrans1:Column 1 == "Alarms" ))[1]] = "Pass/Fail/Alarm";

// Create the Devce values
deviceRow = (dtTrans1 << get rows where( dtTrans1:Column 1 == "Device" ))[1];
For( i = 2, i <= N Cols( dtTrans1 ), i++,
	Column( dtTrans1, i )[deviceRow] = i - 1
);

See if the Transpose 1 data table looks like:

The run the lines 30 - 35

parameterColNamesList = {};
For( i = 1, i <= N Cols( dt ), i++,
	If( Is Missing( Num( Word( 1, Column( dt, i ) << get name, ":" ) ) ) == 0,
		Insert Into( parameterColNamesList, Column( dt, i ) << get name )
	)
);

and see if the parameterColNamesList contains the names of all of the tests in the data table.

Then keep checking the different sections of code, until you find what is not running correctly.

If you can provide to me a copy of the data table you are trying to read in, I would be willing to do the checking myself.