<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Hello,
I have a script that opens multiple data tables and runs an algorithm that creates a new column and assigns a color depending on the result for each of those files. It then concatenates them and makes a subset table depending on which columns you choose.
The problem is that when it concatenates the datatables, the color does not appear in the new concatenated datatable nor does it appear in the subset. My script is below. I also attached the script and the data tables. Thank you.
Names Default To Here( 1 );
// Use 'PickFile()' to get a list of files to open (Windows only)
fileList = Pick File(
"Select one of more files", // Prompt
"$DESKTOP", // Initial folder
{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)
1, // Initial file filter to apply (index of item in the list above)
0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.
Empty(), // Default file
multiple // Multiple - If 'Save Flag' = 0, using a value of 1 allows more than one file to be opened (ignored by OS/X)
);
// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves
If( !Is List( fileList ),
fileList = Eval List( {fileList} )
);
// Open the files and store their corresponding JMP table names
tableNames = {};
For( f = 1, f <= N Items( fileList ), f++,
dttemp = Open( fileList[f] );
Insert Into( tableNames, dttemp << getName );
dttemp = Current Data Table();
dttemp << New Column( "St/Si", Numeric, continous, Format( "Best", 5 ),
Formula( :"Steering rating" / :"Size of car" ) );
dttemp << New Column( "V1-St/Si", Numeric, continous, Format( "Best", 5 ));
For( i = 1, i <= N Row( dttemp ), i++,
If(:"St/Si"[i] > (Col Mean( :"St/Si" ) + (Col Maximum( :"St/Si" ) - Col Mean( :"St/Si" )) * 1/2), :"V1-St/Si"[i] = 3,
If(:"St/Si"[i] > (Col Mean( :"St/Si" ) - (Col Mean( :"St/Si" ) - Col Minimum( :"St/Si" )) * 1/2), :"V1-St/Si"[i] = 2,
If(:"St/Si"[i] > Col Minimum( :"St/Si" ), :"V1-St/Si"[i] = 1, :"V1-St/Si"[i] = 0)
)
);
);
//color row by row
For( i = 1, i <= N Row( dttemp ), i++,
If(:"St/Si"[i] > (Col Mean( :"St/Si" ) + (Col Maximum( :"St/Si" ) - Col Mean( :"St/Si" )) * 1 / 2),
Column( dttemp, "V1-St/Si" ) << Color Cells( 3, {i} ),
If(:"St/Si"[i] > (Col Mean( :"St/Si" ) - (Col Mean( :"St/Si" ) - Col Minimum( :"St/Si" )) * 1 / 2),
Column( dttemp, "V1-St/Si" ) << Color Cells( 9, {i} ),
If( :"St/Si"[i] > Col Minimum( :"St/Si" ),
Column( dttemp, "V1-St/Si" ) << Color Cells( 4, {i} ),
Column( dttemp, "V1-St/Si" ) << Color Cells( 4, {i} )
)
)
);
);
// Append the data tables together
If( f == 1,
dt= dttemp,
dtx = dt<< Join ( With ( dttemp ),
Preserve Main Table Order(),
Output table name("Concatenated Carrat"),
Retain Column Property,
);
Close (dttemp, nosave);
Close (dt, nosave);
dt = dtx
);
);
//Create Column Copier to copy files
dt = Current Data Table();
dt2 = dt << Subset();
If using the column property Value Colors for coloring cells the cell color could be retained in the joined table.
Below is your code replicated but now a color column property is added every time a new "V1-St/Si" column is created:
Names Default To Here(1);
// Use 'PickFile()' to get a list of files to open (Windows only)
fileList = Pick File(
"Select one of more files", // Prompt
"$DESKTOP", // Initial folder
{"JMP Files|jmp;jsl;jrn;csv", "All Files|*"}, // List of file filters to apply (ignored by OS/X)
1, // Initial file filter to apply (index of item in the list above)
0, // Save flag - Specify either a 'Save' or 'Open' window. Set a value of 1 or 0 respectively.
Empty(), // Default file
multiple// Multiple - If 'Save Flag' = 0, using a value of 1 allows more than one file to be opened (ignored by OS/X)
);
// If only a single file is selected, fileList will not be a list, so we need to build it for ourselves
If(!Is List(fileList),
fileList = Eval List({fileList})
);
// Open the files and store their corresponding JMP table names
tableNames = {};
For(f = 1, f <= N Items(fileList), f++,
dttemp = Open(fileList);
Insert Into(tableNames, dttemp << getName);
dttemp = Current Data Table();
dttemp << New Column("St/Si", Numeric, continous, Format("Best", 5), Formula(:"Steering rating" / :"Size of car"));
dttemp << New Column("V1-St/Si",
Numeric,
continous,
Format("Best", 5),
<<Add Column Properties(Set Property("Value Colors", {0 = 4, 1 = 4, 2 = 9, 3 = 3}), Color Cell by Value)
);
For(i = 1, i <= N Row(dttemp), i++,
If(:"St/Si" > (Col Mean(:"St/Si") + (Col Maximum(:"St/Si") - Col Mean(:"St/Si")) * 1 / 2),
:"V1-St/Si" = 3,
If(:"St/Si" > (Col Mean(:"St/Si") - (Col Mean(:"St/Si") - Col Minimum(:"St/Si")) * 1 / 2),
:"V1-St/Si" = 2,
If(:"St/Si" > Col Minimum(:"St/Si"),
:"V1-St/Si" = 1,
:"V1-St/Si" = 0
)
)
)
);
// Append the data tables together
If(f == 1,
dt = dttemp,
dtx = dt << Join(With(dttemp), Preserve Main Table Order(), Output table name("Concatenated Carrat"), Retain Column Property, );
Close(dttemp, nosave);
Close(dt, nosave);
dt = dtx;
);
);
