Apply formula to new columns based on old columns' data

1cooldudeme

Hello everyone,

I am completely new to JMP scripting.

I am trying to create a script that will go to each character column in a table, find all its values (PASS, FAIL, etc..), and create new columns suffixed with "_CODED" using assigned values (1, 2, 3, 4, 5) based on the aforementioned ones.

Here's the script I have right now, that works, but only takes into account a single column. Is there a way I can insert a variable in place of that column name so all columns are accounted for?

dt = Data Table("datatable.jmp");

cols = dt << get column names(Character); //List all character columns

For(i = 1, i <= N Items(cols), i++,

   

   

    dt << New Column(cols << get name || "_CODED",

 

   

        Numeric,

        "Nominal",

        Format("Best", 12),

        Formula(Name Expr(

   

If(:Name("Level1") == "PASS", 1, If(:Name("Level1") == "FAIL", 2, If(:Name("Level1") == "", 3, If(:Name("Level1") == "NOTTESTED", 4, If(:Name("Level1") == "IGNORE", 5, If(:Name("Level1") == "PASS|PASS", 6, If(:Name("Level1") == "IGNORE|IGNORE", 7, If(:Name("Level1") == "NOTTESTED|NOTTESTED", 8))))))))

        ))   

    );

);

Basically, I'd like to know if there is a way to have the script go through with each Character column in a table instead of only Level 1​. I have tried to use cols ​as a variable in place of Level 1​, but this caused the script to not run because quotation marks were needed, or the script would look for a column named "Level 1", which is not the objective.

I took pieces of other scripts and tried to apply them to this one, so please also let me know if there is anything redundant. Thanks a lot.

pmroz

This will do it:

New Table( "Example", Add Rows( 6 ),

     New Column( "aaa", Character, Nominal,

           Set Values( {"PASS", "PASS", "FAIL", "IGNORE", "", "IGNORE"} )

     ),

     New Column( "bbb", Character, Nominal,

           Set Values( {"PASS|PASS", "IGNORE|IGNORE", "PASS", "", "FAIL", "FAIL"} )

     )

);

dt = Data Table("Example");

cols = dt << get column names(Character); //List all character columns

For (i = 1, i <= N Items(cols), i++,

     one_col   = cols[i] << get name;

     coded_col = one_col || "_CODED";

     col_expr = evalinsert(

"\[   dt << New Column("^coded_col^", Numeric, "Nominal",

Format("Best", 12), Formula(

                if (:name("^one_col^") == "PASS", 1,

                     :name("^one_col^") == "FAIL", 2,

                     :name("^one_col^") == "", 3,

                     :name("^one_col^") == "NOTTESTED", 4,

                     :name("^one_col^") == "IGNORE", 5,

                     :name("^one_col^") == "PASS|PASS", 6,

                     :name("^one_col^") == "IGNORE|IGNORE", 7,

                     :Name("^one_col^") == "NOTTESTED|NOTTESTED", 8;

                );

        )

);]\");

    eval(parse(col_expr))

);