Subscribe Bookmark RSS Feed

Joining multiple DataTables and retaining column name

luqi

Community Trekker

Joined:

Nov 12, 2015

​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 1Car type of table 2Car type of concatenated Watch HCar type of table 4Car type of concatenated Watch H2Car type of table 6

Instead, I would this is what I want my preferred result to look like:

Car type of table 1Car type of table 2Car type of table 3Car type of table 4Car type of table 5Car 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​​

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

);

Jim
7 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012


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

Jim
luqi

Community Trekker

Joined:

Nov 12, 2015

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

Solution

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

);

Jim
luqi

Community Trekker

Joined:

Nov 12, 2015

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

txnelson

Super User

Joined:

Jun 22, 2012

Column( dttemp, colnum ) << set name( Char( Column( dttemp, colnum ) << get name ) ||

         " of " || dttemp << get name )

Jim
luqi

Community Trekker

Joined:

Nov 12, 2015

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/*###*/

txnelson

Super User

Joined:

Jun 22, 2012

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

Jim