I need to populate a new column based on results of an if/then statementHi - I am trying to create and populate a group of new columns based on results of an if/then statement.
STEP 1:
I need to create the new empty columns that will be populated. (55 in all)
(Each new column is a numeric, continuous field.)
The name of each column will correspond to the name of each of the 55 compounds in the group followed by "_ppt". E.g. the compound named "4:2 FTS" results in a new column named [4:2 FTS_ppt].
STEP 2:
Row by row, I would populate the new columns using an if/then statement that evaluates three existing columns associated with each of the 55 compounds in the group. Specifically,
if [RES_VALUE_4:2 FTS] >= [RL_4:2 FTS] THEN [4:2 FTS_ppt] = [RES_VALUE_4:2 FTS]
if [RES_VALUE_4:2 FTS] < [RL_4:2 FTS] AND DETECT_FLAG_4:2 FTS] = N AND [RL_4:2 FTS] <= 3 THEN [4:2 FTS_ppt] = 0.0009.
if [RES_VALUE_4:2 FTS] < [RL_4:2 FTS] AND DETECT_FLAG_4:2 FTS] = N AND [RL_4:2 FTS] > 3 THEN [4:2 FTS_ppt] = NULL.
Here are the existing column names with a suffix that is the compound name. I am using JMP Pro 17.0.
Any guidance would be most appreciated!
Automation and ScriptingContent OrganizationRe: I need to populate a new column based on results of an if/then statement- The purpose of the Discussion Community is not to be a source for JMP users to get scripts written at their request. It is there to assist users in learning JMP.
- Given the pseudo code you provided, it is clear that you need to take the time to read the JMP Scripting Guide. It is available in the JMP Documentation Library under the Help pull down menu in JMP.
- Below is an attempt to help you with your issue. It is not tested, since I do not have any of your data. Please take the time to study and learn the code. The functions are well documented in the Scripting Index, and in the JSL Scripting Reference.
Names Default To Here( 1 );
groupList = {"RES_VALUE", "RES_TEXT", "DETECT_FLAG", "RL", "DILUTION_FACTOR"};
dt = Current Data Table();
// Find all of the different compounds. This is done by using the "RES_VALUE" columns
// and stripping off the compound names from each of them
compList = {};
For( i = 1, i <= N Cols( dt ), i++,
colName = Column( dt, i ) << get name;
If( Contains( colName, "RES_VALUE" ) == 1,
Insert Into( compList, Substr( colName, 11 ) )
);
);
// Create the columns
For Each( {comp, i}, compList,
Eval(
Parse(
"dt << New Column( \!"" || compList[i] || "_ppt" || "\!",
numeric,
continuous,
formula(
If( :\!"RES_VALUE "
|| comp || "\!"n >= :\!"RL " || comp || "\!"n,
x = :\!"RES_VALUE " || comp ||
"\!"n
);
If(
:\!"RES_VALUE " || comp ||
"\!"n < :\!"RL " || comp || "\!"n & :\!"DETECT_FLAG " || comp || "\!"n == \!"N\!" &
:\!"RL " || comp ||
"\!"n <= 3,
x = 0.0009
);
If(
:\!"RES_VALUE "
|| comp || "\!"n < :\!"RL " || comp || "\!"n & :\!"DETECT_FLAG " || comp || "\!"n == \!"N\!" &
:\!"RL "
|| comp || "\!"n > 3,
x = .
);
x;
)
);"
)
)
);
Re: I need to populate a new column based on results of an if/then statementThanks Jim. Yes, point well taken. I need to spend more time in the foundations of jsl before jumping into my work.
The code above created all of the columns correctly but the formula is producing only null values ("."). I haven't been able to troubleshoot the error but will keep trying. Thanks again.
Re: I need to populate a new column based on results of an if/then statementI will take a look at it if you can supply some sample data.
Re: I need to populate a new column based on results of an if/then statementThanks Jim. I attached the jmp table below that contains a small subset of rows from my dataset.
Re: I need to populate a new column based on results of an if/then statementTwo things needed to be changed.
- The value of the variable x that is used in the formula needs to be initialized for each rows execution.
- The formula execution need to be UNsuppressed.
Names Default To Here( 1 );
groupList = {"RES_VALUE", "RES_TEXT", "DETECT_FLAG", "RL", "DILUTION_FACTOR"};
dt = Current Data Table();
// Find all of the different compounds. This is done by using the "RES_VALUE" columns
// and stripping off the compound names from each of them
compList = {};
For( i = 1, i <= N Cols( dt ), i++,
colName = Column( dt, i ) << get name;
If( Contains( colName, "RES_VALUE" ) == 1,
Insert Into( compList, Substr( colName, 11 ) )
);
);
// Create the columns
For Each( {comp, i}, compList,
Eval(
Parse(
"dt << New Column( \!"" || compList[i] || "_ppt" ||
"\!",
numeric,
continuous, formula(
x=.;
If( :\!"RES_VALUE "
|| comp || "\!"n >= :\!"RL " || comp || "\!"n,
x = :\!"RES_VALUE " || comp ||
"\!"n
);
If(
:\!"RES_VALUE " ||
comp || "\!"n < :\!"RL " || comp || "\!"n & :\!"DETECT_FLAG " || comp || "\!"n == \!"N\!" &
:\!"RL "
|| comp || "\!"n <= 3,
x = 0.0009
);
If(
:\!"RES_VALUE "
|| comp || "\!"n < :\!"RL " || comp || "\!"n & :\!"DETECT_FLAG " || comp || "\!"n == \!"N\!" &
:\!"RL "
|| comp || "\!"n > 3,
x = .
);
x;
)
);"
)
)
);
dt << Suppress Formula Eval( 0 );
Re: I need to populate a new column based on results of an if/then statementThank you Jim. I've meant to circle back shortly after your reply but have been trying to dubug the code. I have not gotten it to work for some reason. I used the debugger and got this message:
Just wanted you to know I received your input - I will keep trying. Thanks.
Re: I need to populate a new column based on results of an if/then statement
Re: I need to populate a new column based on results of an if/then statementYes! I just got it to work. Thanks again for your help on this!
