246858_en-US

liliconcom

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.

Re: JSL conditional multiple if statement comparing values in two columnsThanks for the great tip. Now I don't have to change my column labels which uses special characters.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)))

Re: JSL conditional multiple if statement comparing values in two columnsThank 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 columnsThank you. This solution worked perfectly too.Re: JSL conditional multiple if statement comparing values in two columns

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

maybe?

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