convert columns to rows

[#1]

data  and desired results

 i would really appreciate your help 

[#2]

ok sure here it is 

[#3]

here is a random sample data

[#4]

 disregard the previous sample, please use this one

[#5]

I ran the script against the subset sample data you provided, and it ran without error and produced an transposed data table.  I did find one small issue, which was the Test Names did not contain the complete name from the input data table.  But other than that, it seems to have run correctly.  

Here is the corrected JSL

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] = Substr( dtTrans2:Label[i], length( dtTrans2:Column 1[i] ) +3 );
	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 );

[#6]

This sample data table has an issue with the Device column.  It is set as a Character Data Type.  If you change it to Numeric, the script works fine.  

[#7]

thanks jim

[#8]

hello jim

 i still find issue with other data table for some reasons i checked the data type and make sure they are all the same. but its not working 

[#9]

The error message that is displayed indicates that the data table that "dtTrans2" points to is not being created.  "dtTrans2" is created from a Transpose(), which requires a list of parameters from your original data table.  The "parameterColNamesList" is created by finding all columns in your original data table that starts with a number followed by a ":".  I suspect your parameters are not named like that in your original data table.  But without seeing the data table, I am only guessing.  If what I am guessing is true, then something needs to be found that identifies the parameter columns.

[#10]

here is one

[#11 · Accepted solution]

The issue was that one or more of the Parameters were input as character data.  The Transpose can not handle mixed data.  So I have added a check to make sure that all detected parameters are numeric.

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 &
		Column( dt, i ) << get modeling type == "Continuous",
		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] = Substr( dtTrans2:Label[i], length( dtTrans2:Column 1[i] ) +3 );
	specs = Column( dt, dtTrans2:Label[i] ) << get property( "spec limits" );
	If( Is Missing( specs["LSL"] ) == 0,
		dtTrans2:Column 3[i] = char(specs["LSL"])
	);
	If( Is Missing( specs["USL"] ) == 0,
		dtTrans2:Column 4[i] = char(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 );