I want to apply two things to a specific column using JSL.
1. Capitalize the first alphabet based on " ".
(exam, ple -> Exam, Ple , JSL TEST -> Jsl Test)
2. In case of having ","
Duplicate the row so that there is one row using the word before "," and one row as the word after "," based on ",".
For example:
- Column1 - "exam, ple" is entered in the first row,
- Replace this with "Exam, Ple",
- Make this two rows and copy the values of Column2 and Column3 as they are.
It would be really appreciated if you could tell me the script to apply the above two rules to the Column.
Thank you in advance! Please be careful of COVID-19.
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);
);
Awesome .. jthi !!!!
The script you suggested completely solved both issues.
Thank you very much.
I hope you have a good New Year's Eve 
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.
