topic Re: Choose output of a new column based on name of the column in Discussions

Choose output of a new column based on name of the column

Yula — Tue, 24 Oct 2023 04:01:54 GMT

I have column names with student names and the class/es that they’ve attended:

Name, Class A1, Class A2, Class A3, Class A, Class B

Where not every Class column has an input. On my script, how can I create a new column where it will only choose the output where:

If a student attended all “A” Classes, the output is from column Class A.

If a student attended Class A1 through A2 or A3, the output is from the class with the highest number (column Class A is empty).

If a student attended Class B, this student did not attend any of the “A” Classes (all empty) and so the output is from Class B.

For now I was able to run a for loop to compile all the column names in a list. However, I’m not sure how to compare all the column names to this logic. I’m relatively new to writing scripts on JMP so any help is appreciated!

Re: Choose output of a new column based on name of the column

jthi — Tue, 24 Oct 2023 05:01:52 GMT

Which JMP version you are using (it changes which type of looping can be used)?

Re: Choose output of a new column based on name of the column

Yula — Tue, 24 Oct 2023 05:53:20 GMT

Hi jthi, I am using version 15!

Re: Choose output of a new column based on name of the column

jthi — Tue, 24 Oct 2023 06:03:43 GMT

This solution looks complicated but the idea is: get list of all class columns and then use data table subscripting WITH that list and get maximum index of found values (so this assumes that the columns are ordered)

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(4),
	Compress File When Saved(1),
	New Column("n", Numeric, "Continuous", Format("Best", 12), Set Values([., ., ., .])),
	New Column("Class A1", Character, "Nominal", Set Values({"A1", "", "A1", ""})),
	New Column("Class A2", Character, "Nominal", Set Values({"", "A2", "A2", ""})),
	New Column("Class A", Character, "Nominal", Set Values({"", "", "", "A"})),
	New Column("Class B", Character, "Nominal", Set Values({"", "", "", "", "B"}))
);
col_list = dt << Get Column Names("String");

class_cols = {};
For(i = 1, i <= N Items(col_list), i++,
	col_name = col_list[i];
	If(Starts With(col_name, "Class "),
		Insert Into(class_cols, col_name);
	);
);

dt << New Column("CLASS GROUP", Character, Nominal, << Set Each Value(
	valcol = Max(Loc(Matrix(!IsMissing(dt[Row(), class_cols])))) + 1;
	dt[Row(), valcol];
));