topic Re: I need to populate a new column based on results of an if/then statement in Discussions

I need to populate a new column based on results of an if/then statement

learning_JSL — Tue, 05 Sep 2023 21:29:02 GMT

Hi - 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!

Re: I need to populate a new column based on results of an if/then statement

txnelson — Wed, 06 Sep 2023 02:58:58 GMT

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 statement

learning_JSL — Wed, 06 Sep 2023 15:24:20 GMT

Thanks 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 statement

txnelson — Wed, 06 Sep 2023 15:55:12 GMT

I 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 statement

learning_JSL — Wed, 06 Sep 2023 16:17:32 GMT

Thanks 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 statement

txnelson — Wed, 06 Sep 2023 18:27:43 GMT

Two 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 statement

learning_JSL — Fri, 08 Sep 2023 19:54:02 GMT

Thank 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

learning_JSL — Fri, 08 Sep 2023 19:55:22 GMT

Re: I need to populate a new column based on results of an if/then statement

learning_JSL — Fri, 08 Sep 2023 20:19:02 GMT

Yes! I just got it to work. Thanks again for your help on this!