Subscribe Bookmark RSS Feed

Copy cell color to new datatable

luqi

Community Trekker

Joined:

Nov 12, 2015

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

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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;

    );

);

4 REPLIES
Solution

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;

    );

);

luqi

Community Trekker

Joined:

Nov 12, 2015

Hello MS,

The appended table still does not have the colors.

ms

Super User

Joined:

Jun 23, 2011

Maybe I've misunderstood your code, but I tried the code in JMP 12 (on OS X), picking the three attached files, and the output table "Concatenated Carrat 2" gets three columns with colored cells.

Do you get any error messages?

luqi

Community Trekker

Joined:

Nov 12, 2015

Hello MS,

Thank you so much! You are the best. That was the right answer. I made a mistake in correcting my code and corrected it after checking my log.