convert columns to rows

steo45

 

 

data  and desired results

 

 i would really appreciate your help 

script.jsl

txnelson

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 );