cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
rverma
Level III

JSL conditional multiple if statement comparing values in two columns

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.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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

View solution in original post

6 REPLIES 6
txnelson
Super User

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

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)))
Jim
Byron_JMP
Staff

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

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

maybe?

JMP Systems Engineer, Health and Life Sciences (Pharma)
rverma
Level III

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

Thank you. This solution worked perfectly too.
rverma
Level III

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

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
Super User

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

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)))
Jim
rverma
Level III

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

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