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
my error....the references to dt should be referencing dttemp
For( colnum = 1, colnum <= N Cols( dttemp ), colnum++,
Column( dttemp, colnum ) << set name( Char( Column( dttemp, colnum ) << get name ) ||
" of Table " || Char( f ) )
);
Lugi,
I would just side step the automatic name changing that JMP will do when joining the tables, and just change their names before the join. Just put in the following before your:joining of the tables:
For( colnum = 1, colnum <= N Cols( dttemp ), colnum++,
Column( dt, colnum ) << set name( Char( Column( dt, colnum ) << get name ) ||
" of Table " || Char( f ) )
);
// Append the data tables together
Hello Jim,
Thank for the help, I am now getting the following error in the log and the tables do not join together anymore.
No Data Table in access or evaluation of 'Column' , Column/*###*/(dt, colnum)
If you want, I can attach the files. Thank You
my error....the references to dt should be referencing dttemp
For( colnum = 1, colnum <= N Cols( dttemp ), colnum++,
Column( dttemp, colnum ) << set name( Char( Column( dttemp, colnum ) << get name ) ||
" of Table " || Char( f ) )
);
Thanks Jim, I should have caught that earlier myself too. One last thing, instead of having "of Table" I would prefer the if it automatically mentioned the data Tables names itself. I should have specified that Car type is the name of the columns in each table and the tables have different names, so for example if the table names are below,
1. Toyota
2. Honda
3. Mercedes
4. Audi
Then the table should look like:
Car Type of Toyota | Car Type of Honda | Car Type of Mercedes | Car Type of Audi |
Column( dttemp, colnum ) << set name( Char( Column( dttemp, colnum ) << get name ) ||
" of " || dttemp << get name )
I have been trying to solve it myself but Error in the log says:
argument should be character in access or evaluation of 'Concat' , Bad Argument( dttemp ), Char( Column( dttemp, colnum ) << get name ) || /*###*/" of " || /*###*/dttemp/*###*/
use the char() function to force that to be a character string
Column( dttemp, colnum ) << set name( Char( Column( dttemp, colnum ) << get name ) ||
" of " || char(dttemp << get name) )