topic Re: Help! Conditional Statements containing Missing Values in Discussions

Help! Conditional Statements containing Missing Values

alexanderbumgar — Mon, 22 Sep 2014 15:17:59 GMT

I am trying to create a column that labels a pair of points
as falling into category “I”, “II”, “III”, or “IV”. The pair of points is
based off of two columns, and some cells have missing values. I am
certain my code is logically correct. Can anyone help me find a working solution?

Below are several of my JSL attempts:

For Each Row(Assign(:Quadrant_Running = If(Is
Missing(:Running_Y), Continue(), Is Missing(:Running_X), Continue(), If(:Running_Y
>= 3 & :Running_X >= 3, "I", If(:Running_Y > 3 &
:Running_X < 3, "II", If(:Running_Y < 3 & :Running_X <
3, "III", "IV"))))))

For Each Row(Assign(:Quadrant_Running = If(Is
Missing(:Running_Y), Continue(), Is Missing(:Running_X), Continue(), :Running_Y
>= 3 & :Running_X >= 3, "I", :Running_Y > 3 &
:Running_X < 3, "II", :Running_Y < 3 & :Running_X <
3, "III", "IV"))

I have also attempted using a conditional “Or” : If(Is
Missing(:Running_Y)|Is Missing(:Running_X), Continue())

And also: If(Is Missing(:Running_Y|:Running_X),
Continue()).

Thanks for any assistance!!!

Re: Help! Conditional Statements containing Missing Values

michael_jmp — Tue, 23 Sep 2014 18:14:23 GMT

Does this script do what you're looking for? I just removed the Assign() function and changed the Continue()'s to empty strings (""), so that Quadrant_Running will be a missing string when one or both of your X/Y columns is missing.

For Each Row(

:Quadrant_Running = If(

Is Missing( :Running_Y ), "",

Is Missing( :Running_X ), "",

If( :Running_Y >= 3 & :Running_X >= 3,

"I",

If( :Running_Y > 3 & :Running_X < 3,

"II",

If( :Running_Y < 3 & :Running_X < 3,

"III",

"IV"

)

Re: Help! Conditional Statements containing Missing Values

michael_jmp — Tue, 23 Sep 2014 18:21:46 GMT

Actually, I think the Continue() from your original script is OK -- you just need to remove the Assign().

Also, your second attempt (without the Assign()) is more compact:

For Each Row(

:Quadrant_Running = If(

Is Missing( :Running_Y ), Continue(),

Is Missing( :Running_X ), Continue(),

:Running_Y >= 3 & :Running_X >= 3, "I",

:Running_Y > 3 & :Running_X < 3, "II",

:Running_Y < 3 & :Running_X < 3, "III",

"IV"

)

Re: Help! Conditional Statements containing Missing Values

alexanderbumgar — Mon, 20 Oct 2014 13:08:33 GMT

Thanks for your help guys!

The assign statement was more compact, and I ended up able to use the Continue() statement.

The issue was the settings on my column, I had not changed them to character values when I originally created the column. Doing so allowed the formula to run!