nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JSL conditional multiple if statement comparing values in two columns

rverma

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.

Accepted answers

txnelson

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)))

All comments

txnelson

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)))

Byron_JMP

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

maybe?

rverma

Thank you. This solution worked perfectly too.

rverma

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.

txnelson

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)))

rverma

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