9599_en-US

liliconcom

Help! Conditional Statements containing Missing Values<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

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!

Re: Help! Conditional Statements containing Missing Values

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

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"

                )

            )

        )

    )

)