Choose Language Hide Translation Bar
Highlighted
rverma
Level II

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

Highlighted
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, Pharm and BioPharm Sciences
Highlighted
rverma
Level II

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

Thank you. This solution worked perfectly too.
Highlighted
rverma
Level II

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.
Highlighted
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
Highlighted
rverma
Level II

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.
Article Labels

    There are no labels assigned to this post.