topic JSL conditional multiple if statement comparing values in two columns in Discussions

JSL conditional multiple if statement comparing values in two columns

rverma — Tue, 11 Feb 2020 04:36:39 GMT

In my data table I want to compare values in two different columns and put results of comparison in a new column. I am able to make it work using a formula but not able to convert it into JSL. I need formula below as JSL script.

If MHD < MTV * -6 => 0

If MHD > MTV * 6 => 0

Else => 1

Here MHD and MTV are two data columns with numeric continuous values. I am comparing values in these two columns for each row.

I tried script below but it does not work.

New Column ("P/F", Numeric, "Nominal", Formula(If(:MHD < :MTV * -6, 0, :MHD > :MTV*6, 0, 1)))

Any guidance will be greatly appreciated.

Re: JSL conditional multiple if statement comparing values in two columns

txnelson — Tue, 11 Feb 2020 05:46:57 GMT

There is a small issue with JSL in handling

:MTV * -6

JSL can be looking at -6 as a subtraction, not as a negative number. Therefore it is wiser to reference it as

:MTV * (-6)

to insure JMP interprets it correctly.

Below are a couple of different ways to set the formula to get the results you want.

New Column ("P/F", Numeric, "Nominal", Formula(If(:MTV *(-6) < :MDH < :MTV * 6,1,0)))
//or
New Column ("P/F", Numeric, "Nominal", Formula(If(:MTV *(-6) < :MDH | :MDH > :MTV * 6,0,1)))

Re: JSL conditional multiple if statement comparing values in two columns

Byron_JMP — Tue, 11 Feb 2020 12:23:51 GMT

Formula( If( :MDH < :MTV * -6 | :MDH > :MTV * 6, 0, 1 )

maybe?

Re: JSL conditional multiple if statement comparing values in two columns

rverma — Wed, 12 Feb 2020 02:26:37 GMT

Thank you. This solution worked perfectly too.

Re: JSL conditional multiple if statement comparing values in two columns

rverma — Wed, 12 Feb 2020 04:13:41 GMT

Thank you for the quick response and great ideas to solve the issue. My script is working now. One more thing I noticed was that if any of the column labels had a '-' or '&' then script would fail to work. Once those special characters were removed script worked without any issues.

Re: JSL conditional multiple if statement comparing values in two columns

txnelson — Wed, 12 Feb 2020 04:27:48 GMT

JSL is interpreting the & etc. as a computational element, not as something that is part of a name. Take a look at the :Name() function that you can place around a column name to make sure JSL interprets the column name correctly..

If you MDH column was named MDH/PQR JSL would interpret the name as a mathematical calculation of dividing a variable called MDH by a variable called PQR. But if you reference it as:

:Name( "MDH/PQR" )

all will work fine.

In your code it would look like

New Column ("P/F", Numeric, "Nominal", Formula(If(:MTV *(-6) < :Name("MDH/PQR") < :MTV * 6,1,0)))

Re: JSL conditional multiple if statement comparing values in two columns

rverma — Wed, 12 Feb 2020 05:16:33 GMT

Thanks for the great tip. Now I don't have to change my column labels which uses special characters.