Imported Variable loses formatting when stacked

[#1]

Hi,

when l import a dataset with the following code:

Names Default To Here( 1 );
Clear Symbols();

pb = Page Break Box();

fileopen= "\\prusntyfil01\home$\sv001241\Desktop\FCCC\Scripts\Data tables for statistical analysis 07NOV22.xlsx";

//dt=open(fileopen,private);
	dt = Open(
		 fileopen, 
		 Worksheets( "USP & DSP Subtype Dependent" ),
		 
		Worksheet Settings(
				1,
				Has Column Headers( 1 ),
				Number of Rows in Headers( 1 ),
				Headers Start on Row( 1 ),
				Data Starts on Row( 2 ),
				Data Starts on Column( 1 ),
				Data Ends on Row( 262 ),
				Data Ends on Column( 14 ),
				Replicated Spanned Rows( 1 ),
				Replicated Spanned Headers( 0 ),
				Suppress Hidden Rows( 1 ),
				Suppress Hidden Columns( 1 ),
				Suppress Empty Columns( 1 ),
				Treat as Hierarchy( 0 ),
				Multiple Series Stack( 0 ),
				Import Cell Colors( 0 ),
				Limit Column Detect( 0 ),
				Column Separator String( "" )
			),
		); 

////Had to change the data type of some variables, some of the variables have operators in the name had to change the names of some variables then change data types////	
///When l tried to work with the variables with an operator in there name such as "/" and "%" i had problems so tried to change the names of Variables with/////
///either brackets or a operator in the variable name//////

dt :Specific Purity << Data type (Numeric) << Set Modeling Type (continuous); 

col = Column( 2 );
	col << Set Name( "Scale Area" );
	
col = Column( 4 );
	col << Set Name( "Subtype" );
	
col = Column( 6 );
	col << Set Name( "ICD 106 cells mL" );
	
col = Column( 7 );
	col << Set Name( "HA µg mL" );
///need to format the below column as percent/////
col = Column( 8 );
	col << Set Name( "HA Step Yield percent" );

dt :HA Step Yield percent << Data type (Numeric) << Format( "Percent", 12, 1 )  << Set Modeling Type (continuous) << Set Selected;

col = Column( 9 );
	col << Set Name( "Total Protein BCA ug mL" );
	
dt :Total Protein BCA ug mL << Data type (Numeric) << Set Modeling Type (continuous);

col = Column( 11 );
	col << Set Name( "HA by SRD µg mL" );

dt :HA by SRD µg mL  << Data type (Numeric) << Set Modeling Type (continuous);

col = Column( 12 );
	col << Set Name( "HCP by ELISA µg mL" );

dt :HCP by ELISA µg mL << Data type (Numeric) << Set Modeling Type (continuous);

col = Column( 13 );
	col << Set Name( "HCP µg µg" );

dt :HCP µg µg  << Data type (Numeric) << Set Modeling Type (continuous);

some of the variables import as ordinal or nominal and I need to correct for this which is fine, however the variable: HA Step Yield percent loses its formatting when l then stack the data using the following code, how can I prevent this from happening??

dt2 = dt << stack(
		columns(:ICD 106 cells mL, :HA µg mL, :HA Step Yield percent,
				:Total Protein BCA ug mL, :Specific Purity, :HA by SRD µg mL,
				:HCP by ELISA µg mL, :HCP µg µg),
		Source Label Column( "Quality Attribute" ),
		Stacked Data Column( "Data" ),
		output table name( "stacked" )
);

[#2 · Accepted solution]

Hi @Mickyboy ,

It sounds like you want different rows within a single column to have different properties. That is not going to be possible unfortunately. Column properties such as Format apply to the whole column. That is the nature of the stacked format. If you want the different variables to have different properties, you would need to leave them in the wide format.

Why do you need the Percent Format? Perhaps you can just do a multiplication prior to stacking.

Sorry, but I hope that helps.

Phil

[#3]

Thanks Phil,

I did think of multiplying by 100 prior to stacking if l couldn't find a solution, thanks for your advice, appreciate it.