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!

 

ScriptingRe: 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 (ismissing (:column A), empty(),
:column A< 500 & :column B == 200, 1,
:columnA>500, 0
))

 

 

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

@Campbell_Ted_R 

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

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

@Campbell_Ted_R 

try the edited version

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

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