I have a script(below) that opens multiple data tables and runs a script on each one then joins(concatenates horizontally) them together. The problem I have now is the last part where I append/join the tables together in the code. My code currently joins the table together but after 2 concurrent data tables are joined, the third one has the name concatenated watch dog in it's column instead of the retaining the name of the table it opened. Example of what I get is shown below:
| Car type of table 1 | Car type of table 2 | Car type of concatenated Watch H | Car type of table 4 | Car type of concatenated Watch H2 | Car type of table 6 |
|---|
| | | | | |
Instead, I would this is what I want my preferred result to look like:
| Car type of table 1 | Car type of table 2 | Car type of table 3 | Car type of table 4 | Car type of table 5 | Car type of table 6 |
|---|
| | | | | |
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();
//Create new columns and add color coding properties depending on valeus gotten
dttemp << New Column("Max.", Numeric, continous, Format("Best", 5), <<Add Column Properties(Set Property("Value Colors", {0 = 4, 1 = 4, 2 = 9, 3 = 3}), Color Cell by Value), << Set Property( "Missing Value Codes", {0} ));
dttemp << New Column("Min.", Numeric, continous, Format("Best", 5), <<Add Column Properties(Set Property("Value Colors", {0 = 4, 1 = 4, 2 = 9, 3 = 3}), Color Cell by Value), << Set Property( "Missing Value Codes", {0} ));
dttemp << New Column("Amp.", Numeric, continous, Format("Best", 5), <<Add Column Properties(Set Property("Value Colors", {0 = 3, 1 = 3, 2 = 9, 3 = 4}), Color Cell by Value), << Set Property( "Missing Value Codes", {0} ));
dttemp << New Column("Real/Model.", Numeric, continous, Format("Best", 5), <<Add Column Properties(Set Property("Value Colors", {0 = 4, 1 = 4, 2 = 9, 3 = 3}), Color Cell by Value), << Set Property( "Missing Value Codes", {0} ));
dttemp << New Column("Osc.", Numeric, continous, Format("Best", 5), <<Add Column Properties(Set Property("Value Colors", {0 = 4, 1 = 4, 2 = 9, 3 = 3}), Color Cell by Value), << Set Property( "Missing Value Codes", {0} ));
For( i = 1, i <= N Row( dttemp ), i++,
If(:"Max.GPM/GSF"[i] > (Col Mean(:"Max.GPM/GSF") + (Col Maximum(:"Max.GPM/GSF")-((Col Maximum(:"Max.GPM/GSF")+Col Mean(:"Max.GPM/GSF"))*1/2))*1/2 ),:"Max."[i] = 3,
If( :"Max.GPM/GSF"[i] > (Col Mean(:"Max.GPM/GSF")-(Col Mean(:"Max.GPM/GSF")-Col Minimum(:"Max.GPM/GSF"))*1/2),:"Max."[i] = 2,
If( :"Max.GPM/GSF"[i] <= (Col Mean(:"Max.GPM/GSF")-(Col Mean(:"Max.GPM/GSF")-Col Minimum(:"Max.GPM/GSF"))*1/2),
:"Max."[i] = 1,
:"Max."[i] = 0,
)
)
);
If( :"Min.GPM/GSF"[i] > (Col Mean(:"Min.GPM/GSF") + (Col Maximum(:"Min.GPM/GSF")-((Col Maximum(:"Min.GPM/GSF")+Col Mean(:"Min.GPM/GSF"))*1/2))*1/2 ),:"Min."[i] = 3,
If( :"Min.GPM/GSF"[i] > (Col Mean(:"Min.GPM/GSF")-(Col Mean(:"Min.GPM/GSF")-Col Minimum(:"Min.GPM/GSF"))*1/2),:"Min."[i] = 2,
If( :"Min.GPM/GSF"[i] > Col Minimum( :"Min.GPM/GSF" ),
:"Min."[i] = 1,
:"Min."[i] = 0
)
)
);
If( :"Amp.GPM/GSF"[i] > (Col Mean(:"Amp.GPM/GSF") + (Col Maximum(:"Amp.GPM/GSF")-((Col Maximum(:"Amp.GPM/GSF")+Col Mean(:"Amp.GPM/GSF"))*1/2))*1/2),:"Amp."[i] = 1,
If( :"Amp.GPM/GSF"[i] > (Col Mean( :"Amp.GPM/GSF" ) - (Col Mean( :"Amp.GPM/GSF" ) - Col Minimum( :"Amp.GPM/GSF" )) * 1 / 2),:"Amp."[i] = 2,
If( :"Amp.GPM/GSF"[i] > Col Minimum( :"Amp.GPM/GSF" ),
:"Amp."[i] = 3,
:"Amp."[i] = 0
)
);
);
If( :"Real/Model"[i] > (Col Mean(:"Real/Model") + (Col Maximum(:"Real/Model")-((Col Maximum(:"Real/Model")+Col Mean(:"Real/Model"))*1/2))*1/2),:"Real/Model."[i] = 3,
If( :"Real/Model"[i] > (Col Mean( :"Real/Model" ) - (Col Mean( :"Real/Model" ) - Col Minimum( :"Real/Model" )) * 1 / 2),:"Real/Model."[i] = 2,
If( :"Real/Model"[i] > Col Minimum( :"Real/Model" ),
:"Real/Model."[i] = 1,
:"Real/Model."[i] = 0
)
)
);
If( :"st-Dev."[i] > (Col Mean(:"st-Dev.") + (Col Maximum(:"st-Dev.")-((Col Maximum(:"st-Dev.")+Col Mean(:"st-Dev."))*1/2))*1/2), :"Osc."[i] = 3,
If( :"st-Dev."[i] > (Col Mean( :"st-Dev." ) - (Col Mean( :"st-Dev." ) - Col Minimum( :"st-Dev." )) * 1 / 2), :"Osc."[i] = 2,
If( :"st-Dev."[i] > Col Minimum( :"st-Dev." ),
:"Osc."[i] = 1,
:"Osc."[i] = 0
)
)
);
);
dttemp << New Column( "GPM(day)/GSF", Numeric, continous, Format( "Best", 5 ), Formula( :"Max.GPM/GSF" * 12 ) );
//Weights Given
w1 = 1;
w2 = 1;
w3 = 0.5;
w4 = 0;
w5 = 0.5;
dttemp << New Column("Total_Vs", Numeric, continous, Format("Best", 5),
Formula(:"Max."*w1 + :"Min."*w2 + :"Amp."*w3 + :"Real/Model"*w4 + :"Osc."*w5),
<< Add Column Properties( Set Property ("Value Colors", {For( i = 1, i <= N Row( dttemp ), i++,
If(Column( dttemp, "Total_Vs" )[i] >= 8, Column( dttemp, "Final Result" ) << Color Cells( red, {i} ),
Column( dttemp, "Total_Vs" )[i] >= 4, Column( dttemp, "Final Result" ) << Color Cells( yellow, {i} ),
Column( dttemp, "Total_Vs" ) << Color Cells( green, {i} )
) )} ), Color Cell by Value )
);
//Remove Columns
dttemp= Current Data Table();
Column( dttemp, "GreenLine" ) << hide( 1 );
dttemp = Current Data Table();
Column( dttemp, "GreenLineIdx" ) << hide( 1 );
// Append the data tables together
If( f == 1,
dt= dttemp,
dtx = dt<< Join (With ( dttemp ),
Preserve Main Table Order(1),
Output table name("Concatenated Watch H"),
Retain Column Property);
Close (dttemp, no save);
Close (dt, no save);
dt = dtx
);
);
Help: jordanhiller@jmp MS
