Add transform formula which can be used to quickly create labelling based on comparison results

[jthi]

What inspired this wish list request? 

I quite commonly have to create labelling columns depending on the row values on one column. For example I might have ERROR_DESCRIPTION column and when it has a value it means error happened else test was ok. So I would like to have FAILURE labels formula :ERRORDESCRIPTION != "" -> "1" else "0".  And I would like to be able to do this with New Formula Colum and as Transform columns from Filter Col Selectors in different platforms.

What is the improvement you would like to see? 

I would like to see possibility to create new formula column (and transform column) to create such labellings based on row values.

This example does combine two types into one, but idea would be that depending on the Data Type of column, different selections should be available. UI can be made more user friendly fairly easy (and should be), for example by disabling selections until Row Value has been selected. And maybe if column modelling type is continuous the combo box could be slider box

and in this case created columns would be something like this:

dt << New Column("Compare[age]", Character, Nominal, Formula(
	If(:age >= 14, 1,
		0
	)
));

dt << New Column("Compare[name]", Character, Nominal, Formula(
	If(:name == "ALFRED", "1",
		:name == "CAROL", "2",
		"0"
	)
));

Demo UI script:

View more...

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

Summarize(dt, col_options_num = By(:age));
Insert Into(col_options_num, "<SELECT>", 1);
numeric_options = {"==", ">=", ">", "<=", "<"};

Summarize(dt, col_options_char = By(:name));
Insert Into(col_options_char, "<SELECT>", 1);
character_options = {"==", "!="};

lub_expr_numeric = Expr(Lineup Box(N Col(3), 
	Combo Box(col_options_num, << Set Function(function({this},
		If(this << get selected != "<SELECT>",
			((this << parent) << parent) << Append(lub_expr_numeric);
		);
	))),
	Combo Box(numeric_options),
	Number Edit Box(.)
));

lub_expr_character = Expr(Lineup Box(N Col(3), 
	Combo Box(col_options_char, << Set Function(function({this},
		If(this << get selected != "<SELECT>",
			((this << parent) << parent) << Append(lub_expr_character);
		);
	))),
	Combo Box(character_options),
	Text Edit Box("")
));

// could also be built with Table Box
nw = New Window("Demo", << modal,
	H List Box(
		Panel Box("Numeric - age",
			Lineup Ruler Box(
				Widths({100,70,50}),
				Lineup Box(N Col(3),
					Text Box("Row Value"),
					Text Box("Compare"),
					Text Box("Value")
				),
				V List Box(
					lub_expr_numeric,
				),
				Text Box("..."),
				Lineup Box(N Col(3),
					Text Box("Else"),
					Combo Box({""}, << Enabled(0)),
					Number Edit Box(.)
				)
			)
		),
		Spacer Box(Size(50,0)),
		Panel Box("Character - name",
			Lineup Ruler Box(
				Widths({100,70,50}),
				Lineup Box(N Col(3),
					Text Box("Row Value"),
					Text Box("Compare"),
					Text Box("Value")
				),
				V List Box(
					lub_expr_character,
				),
				Text Box("..."),
				Lineup Box(N Col(3),
					Text Box("Else"),
					Combo Box({""}, << Enabled(0)),
					Text Edit Box("")
				)
			)
		),
		Panel Box("Actions",
			Button Box("OK",
				num_vals = .
			),
			Button Box("Cancel")
		)
	)
);

Why is this idea important? 

Would make it much faster to create transform labelling columns which are not dependent on selections but rather on row values in the column.

[hogi]

Workaround for the while (for the right/character case):
- generate a dummy column with just formula = source column name

- select the column name in the formula and shift click Match --> Match expression is added and  automatically filled will all available values

(if there are too many different values in the column, then it's easier to add the 2-3 desired ones manually -- instead of deleting the other 5000 ones :)

[Sarah-Sylvestre]

Hi @jthi, thank you for your suggestion! We have captured your request and will take it under consideration.

[hogi]

the availability of a Is Missing in the new formula column menu would be an amazingly useful first step.

- just a very simple comparison

- just for a single column (not 2 columns like in this example)

... but sooo helpful - every 2nd JMP user could use it several times a day :)