The script does solve both issues, but in general it is good idea to try to do it also interactively in JMP. You can even get JMP to do the Titlecase for you through Recode platform:
or create new formula column:
And with JMP16 quite a few operations you perform, you can find from enhanced log to easily automate the process.
Awesome .. jthi !!!!
The script you suggested completely solved both issues.
Thank you very much.
First one can be done with Titlecase(). Second one can be done for example by looping over rows with For Each or interactively directly from JMP.
Interactive solution:
1. Starting table
2. Select Col_a and go to Cols / Utilities / Text to Columns and use , as delimiter
3. Next go to Tables / Stack. Start with settings like this and try it out:
result table:
4. Delete Label column and select one of empty rows in Data column -> right click -> select matching cells -> delete rows
With scripting you can do for example something like this (requires JMP16 due to For Each, but it can be changed to For-loop if necessary). This can also be made more simple if it is known how many columns you have and how many values there can be at the string which is used to dublicate rows.
Names Default To Here(1);
dt = New Table("Untitled",
Add Rows(2),
New Column("Col_a", Character, "Nominal", Set Values({"exam, ple", "JSL TEST"})),
New Column("Col_b", Numeric, "Continuous", Format("Best", 12), Set Values([123, 345])),
New Column("Col_3", Numeric, "Continuous", Format("Best", 12), Set Values([222, 333]))
);
Column(dt, "Col_a") << Set Each Value(Titlecase(:Col_a));
//using collector table:
dt_collector = dt << Subset(
Rows([1]),
Selected columns only(0)
);
dt_collector << Delete Rows([1]);
For Each Row(dt,
row_vals = dt[Row(), 0];
If(Contains(row_vals[1], ","),
new_items = Words(row_vals[1], ",");
For Each({new_item}, new_items,
dt_collector << Add Row(1);
row_to_add = Insert(Eval List({Trim Whitespace(new_item)}), Remove(row_vals, 1));
dt_collector[N Rows(dt_collector), 0] = row_to_add;
);
, //else
dt_collector << Add Row(1);
dt_collector[N Rows(dt_collector), 0] = row_vals;
);
);
//adding to same table
//get rows with ,
rows_to_add = dt << Get Rows Where(Contains(:Col_a, ","));
For Each({new_row}, rows_to_add,
new_items = Words(dt[new_row, "Col_a"], ",");
dt[new_row, "Col_a"] = Trim Whitespace(new_items[1]);
Remove From(new_items, 1);
For Each({new_item}, new_items,
dt << Add Rows(1);
row_to_add = Insert(Eval List({Trim Whitespace(new_item)}), Remove(dt[new_row, 0], 1));
dt[N Rows(dt_collector), 0] = row_to_add;
);
show(new_items);
);
