425006_en-US

liliconcom

need jsl formula to account for empty cell in conditional if statement

Hi - I am new to jsl scripting and am trying to construct a jsl statement as follows: 

if column A is empty, column C = empty,

else if column A < 500 and column B = 200, column C = 0,

else if column A>500, column C = 1.

How would I frame a jsl formula to do this?  Thanks!

 

Automation and ScriptingQuality and Process EngineeringRe: need jsl formula to account for empty cell in conditional if statement

Thank you Craige.  Helpful tips to debug the formulas.  I appreciate it!

Re: need jsl formula to account for empty cell in conditional if statement

Thanks Ron.  It's working now!  

Re: need jsl formula to account for empty cell in conditional if statement

Watch out for the edge cases:

 

• A == 500, exactly
• A<500 and B != 200

 

It might be worth adding 999 as an otherwise value, just to get your attention if something slips through. If Secrets 

 

If B is a missing value, the B!=200 test will not be TRUE or FALSE. The examples above will probably do what you expect, but if the B==200 is just before the final otherwise value (999, for example), and B is missing, it won't get the otherwise value (because not TRUE or FALSE) -- it gets missing.

 

Re: need jsl formula to account for empty cell in conditional if statement

I'll have to give this a try in the morning.  Thanks!

Re: need jsl formula to account for empty cell in conditional if statement

is this correct?

New Table( "Untitled",
 Add Rows( 12 ),
 New Column( "A",
  Numeric,
  "Continuous",
  Format( "Fixed Dec", 12, 0 ),
  Set Values( [., ., 400, 400, 550, 550, ., ., 400, 400, 550, 550] )
 ),
 New Column( "B",
  Numeric,
  "Continuous",
  Format( "Fixed Dec", 5, 0 ),
  Set Values( [200, 61, 200, 66, 200, 60, 200, 51, 200, 61, 200, 65] )
 ),
 New Column( "C",
  Numeric,
  "Ordinal",
  Format( "Best", 10 ),
  Formula( If( Is Missing( :A ), ., :A < 500 & :B == 200, 0, :A > 500, 1 ) ),
  Set Selected
 )
);

Re: need jsl formula to account for empty cell in conditional if statement

It now recognizes the empty cells and correctly populates them as empty (yay), correctly accounts for the 2nd condition and sets the 0's appropriately,  but it does not make it to the third (else if) condition.  That is, it is never 1 even though there are many instances when it should be.   

Re: need jsl formula to account for empty cell in conditional if statement

@learning_JSL 

try the edited version

Re: need jsl formula to account for empty cell in conditional if statement

Thank you Ron.  The "empty" condition is not registering for some reason.  That is, if :A is empty, :C is 1. 

??

Re: need jsl formula to account for empty cell in conditional if statement

@learning_JSL 

try this:

names default to here (1);

dt = Current data table();
dt << new column ("C","Numeric", "ordinal", Formula (If(
  Is Missing( :A ),.,
  :A < 500 & :B == 200, 0,
  :A > 500, 1
 )));

Re: need jsl formula to account for empty cell in conditional if statement

This is what I came up with after a thorough search of the jsl index, help menu, and google and lots of trial and error attempts....but it has not worked.   My formula for column C is:

formula(
 If(
  Is Missing( :column A ), Empty(),
  :column A < 500 & :column B == 200, 1,
  :columnA > 500, 0
 )
)