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

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

learning_JSL — Fri, 09 Jun 2023 18:01:54 GMT

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!

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

learning_JSL — Sun, 10 Oct 2021 01:59:32 GMT

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

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

ron_horne — Sat, 09 Oct 2021 23:18:59 GMT

@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

learning_JSL — Sat, 09 Oct 2021 23:13:15 GMT

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

ron_horne — Sat, 09 Oct 2021 23:17:32 GMT

@learning_JSL

try the edited version

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

learning_JSL — Sat, 09 Oct 2021 23:51:06 GMT

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

ron_horne — Sun, 10 Oct 2021 00:30:20 GMT

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

learning_JSL — Sun, 10 Oct 2021 00:46:22 GMT

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

Craige_Hales — Sun, 10 Oct 2021 01:48:31 GMT

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

learning_JSL — Sun, 10 Oct 2021 13:20:44 GMT

Thanks Ron. It's working now!

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

learning_JSL — Sun, 10 Oct 2021 13:21:55 GMT

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