- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Add a new column with a function similar to vlookup in Excel
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Add a new column with a function similar to vlookup in Excel
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)
));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Add a new column with a function similar to vlookup in Excel
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Add a new column with a function similar to vlookup in Excel
That should have been by Fermentation, not by Treatment.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Add a new column with a function similar to vlookup in Excel
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Add a new column with a function similar to vlookup in Excel
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Add a new column with a function similar to vlookup in Excel
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)
));