How to use "conditional statements" to select data rows to then execute e.g. simple Col A - Col CNeed some help!
I want to select data rows from 3 or 4 columns (based on CONDITIONAL statements, then execute rather simply formula e.g. if conditions met in selecting the correct rows
If(
:YEAR == 2020, :CROP | Corn | Soybean,
:TYPE == IF, :"Mean(YIELD(BU/A)_OR, 13)"n - :"Mean(YIELD(BU/A)_OR, 8)"n,
:ELSE == :EMPTY (I prefer to not use ELSE statement if it is possible....just want the above conditional statements to be met to know exactly what gets added in new column via formula)
OR,
If(
:YEAR == 2020, :CROP | Corn | Soybean,
:TYPE == FOLIAR, :"Mean(YIELD(BU/A)_OR, 4)"n - :"Mean(YIELD(BU/A)_OR, 1)"n,
OR,
If(
:YEAR == 2021, :CROP | Corn | Soybean,
:TYPE == IF, :"Mean(YIELD(BU/A)_OR, 6)"n - :"Mean(YIELD(BU/A)_OR, 2)"n
OR,
If(
:YEAR == 2021, :CROP | Corn | Soybean,
:TYPE == FOLIAR, :"Mean(YIELD(BU/A)_OR, 3)"n - :"Mean(YIELD(BU/A)_OR, 1)"n
)
ScriptingRe: How to use "conditional statements" to select data rows to then execute e.g. simple Col A - Col CI am having trouble interpreting your pseudo code.
I assume you have a numeric column called YEAR and that you are checking to see if it has the value 2020
Do you have a column named:
CROP | Corn | Soybean
or are you wanting a column named CROP to have a value of Corn or Soybean
It appears you have a column named TYPE and you are checking to see if it has the value of "IF"?
and if the value is equal to "IF" then you want to find the difference between a column named Mean(YIELD(BU/A)_OR, 13) and a column named Mean(YIELD(BU/A)_OR, 8)
or do you want to calculate the mean for column YIELD
and finally you are referencing a column named ELSE and determining if is equal to a column named EMPTY.
I am sure this is not what you want.
Can you provide a sample of what the data table is and then describe in words, what you want.
I also suggest that you take the time to read the Scripting Guide. It is available in the JMP Documentation Library, under the Help pull down menu.
Re: How to use "conditional statements" to select data rows to then execute e.g. simple Col A - Col CHi Jim, thanks for replying and I appreciate any help you can offer and sorry for the confusion - I'll try to clarify and simplify the type of formula I am wanting.
I have a column named YEAR and values are either 2020 or 2021.
I have a another column named CROP and values are either Corn or Soybean.
I have another column named TYPE and values are either Foliar or IF (i.e., in-furrow)
I have another 10 columns that represent the Mean Yield of Treatments 1 through 10 (TRT1, TRT2,......,TRT10).
I want a formula to calculate the added value of each Treatment (different product dose rates) vs. the Control Treatment (0 rate). Here's the kicker, the Treatment numbers (representing dose rates 0, 1, 4, 8, 16, 32, 64 FL OZ/acre) differed between the 2 years and between CROP and TYPE. For example, 16 FL OZ/acre product dose rate was TRT5 in 2020 for both Corn and Soybean "IF" i.e., represents in-furrow trials, and was TRT3 in both Corn and Soybean "Foliar" trials in 2021.
So I have 8 possible combinations of YEAR (2) x CROP (2) x TYPE (2) and I'd like 1 formula to fill in the values for change in bu./acre for a given product dose rate vs. the Control Treatment (0 rate)....so for example, I create a new column named "16FLOZ_AddedValue" and want the formula to do:
If YEAR = 2020, CROP=Corn, TYPE=Foliar, the formula needs to return value of "TRT3" minus column "TRT1"; however,
If YEAR = 2020, CROP=Corn, TYPE=IF (i.e. in-furrow), the formula needs to return value of "TRT5" minus column "TRT1" in same column named "16FLOZ_AddedValue"; however,
If YEAR = 2021, CROP=Corn, TYPE=Foliar, the formula needs to return value of "TRT4" minus column "TRT1"" in same column named "16FLOZ_AddedValue";
etc. (sorry, I do not know how to string the "IF"s together in 1 formula).
Regarding the ELSE comment, that just pertains to my desire to not have the formula do any sort of hidden action steps when the "IF" combination is not met. In other words, for example, if YEAR=2020, CROP=Corn, TYPE=Foliar, then only calculate TRT3-TRT1 and omit any ELSE type of action, i.e. leave all remaining cell values untouched; and move to the next combo IF statements that might direct the formula to take TRT5-TRT1, etc.
