topic Add a new column with a function similar to vlookup in Excel in Discussions

Add a new column with a function similar to vlookup in Excel

VarianceWalrus9 — Fri, 03 Nov 2023 19:03:39 GMT

I'm trying to figure out how to add a new column to a table that will populate with data dependent on other data in that row. We have several fermentations in a data frame. Each one has a blank value. I'd like to have the blank value from each fermentation in a new column so that I can subtract it from the treatment value. Also if there's a better way to do this than creating a new column that would be great too! Thanks.

Made up example data:

Fermentation	Treatment	Value	Fermentation Blank Value
A	1	98	6
A	2	73	6
A	Blank	6	6
B	1	84	5
B	2	86	5
B	Blank	5	5

Re: Add a new column with a function similar to vlookup in Excel

dale_lehman — Fri, 03 Nov 2023 19:16:01 GMT

Is the "Value" when Treatment = Blank always less than the Value when Treatment is not Blank? If so, you can just use the ColMin function, by Treatment. If not, I suppose a somewhat clumsy way to do something similar is first create a column that makes Treatment not equal to Blank = 1 and Treatment = Blank equal to zero, and then use the ColMin By approach.

Re: Add a new column with a function similar to vlookup in Excel

jthi — Fri, 03 Nov 2023 19:16:12 GMT

Where would you have the value if not in a new column?

Here is one formula how you could add it as a new column

Col Sum(If(:Treatment == "Blank", :Value, .), :Fermentation)

Re: Add a new column with a function similar to vlookup in Excel

VarianceWalrus9 — Fri, 03 Nov 2023 20:10:37 GMT

When I try this expression I get an error saying

Expecting numeric column in access or evaluation of 'Col Sum', Col Sum/*###*/IF(:Treatment == "Blank", :Value,.):Fermentation)

The column "Value" is numeric continuous, so I'm not sure what part of the expression it doesn't like

Re: Add a new column with a function similar to vlookup in Excel

jthi — Fri, 03 Nov 2023 20:49:35 GMT

Most likely the Value column in your datatable isn't formatted as numeric (most likely a character).

Here is a script with example table that does work. Look at the left side for columns and their modeling types, Value is continuous (you cannot have data type = character, modeling type = continuous in JMP (you could have numeric nominal though which you cannot see directly from the modeling types...).

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("Fermentation", Character, "Nominal", Set Values({"A", "A", "A", "B", "B", "B"})),
	New Column("Treatment",
		Character(16),
		"Nominal",
		Set Values({"1", "2", "Blank", "1", "2", "Blank"})
	),
	New Column("Value",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([98, 73, 6, 84, 86, 5])
	),
	New Column("Fermentation Blank Value",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([6, 6, 6, 5, 5, 5])
	)
);

dt << New Column("Val", Numeric, Continuous, Formula(
	Col Sum(If(:Treatment == "Blank", :Value, .), :Fermentation)
));

Re: Add a new column with a function similar to vlookup in Excel

dale_lehman — Sat, 04 Nov 2023 00:03:26 GMT

That should have been by Fermentation, not by Treatment.