topic Re: Populating a new column using a conditional formula in Discussions

Populating a new column using a conditional formula

dfusco923 — Fri, 09 Jun 2023 22:01:44 GMT

I am attempting to populate a new column ( :Prog_ ) with either "A" or "B" based upon the value of (:Assign Month). :Assign Month is a numeric, continuous, m/d/y variable.

The below script result in the :Prog_ column being populated but not with the values I am trying to populate the :Prog_ column with.

I'd like to populate the :Prog_ column rows with an "A" if :Assign Month <= "06/15/2016"

I'd like to populate the :Prog_ column rows with a "B" if :Assign Month is > 06/15/2016"

Here is the script I've tried to use (unsuccessfully) so far:

CT << New Column("Prog_", Character);
For each row(Prog_ = if( Char(Format(:Assign Month,"m/d/y")) <= "06/15/2016", "A", "B" ));

CT << New Column("Prog_", Character);
For each row(Prog_ = if( :Assign Month <= 06/15/2016, "A", "B" ));

Re: Populating a new column using a conditional formula

Thierry_S — Sun, 03 Jan 2021 22:10:37 GMT

Hi,
First, make sure that the :Assign Month column is formatted as a Numerical, Continuous Date format; I'm guessing by your use of quotes in your formula indicates that it is not currently the case.
Second, you do not need to tell JMP to look at each row, it does it by default.
Third, the formula in the :Prog_ column should simply be:

If( :Column 1 <= Date MDY( 16, 05, 2016 ),
    "A",
    "B"
)

Of note, if this is part of a JSL script, the function should be included in the New Column statement (check the Scripting help for the exact syntax.
Best,
TS

Re: Populating a new column using a conditional formula

txnelson — Mon, 28 Dec 2020 10:31:19 GMT

Here is how I typically handle this type of issue. The Informat() function converts a a string into a numeric representation of the date, which then can easily be compared to JMP DateTime values.

CT = Current Data Table();
CT << New Column( "Prog_", Character );
For Each Row( Prog_ = If( :Assign Month <= Informat( "06/15/2016", "mdy" ), "A", "B" ) );

And to add to @Thierry_S you could avoid the For Each Row() if you created a formula column

CT = Current Data Table();
CT << New Column( "Prog_",
	Character,
	formula(
		If( :Assign Month <= Informat( "06/15/2016", "mdy" ),
			"A",
			"B"
		)
	)
);

Re: Populating a new column using a conditional formula

Mark_Bailey — Mon, 28 Dec 2020 15:15:51 GMT

Your original variable is numeric and represents a JMP date and time value. There is no need to convert it to a formatted character string. You can specify the time threshold directly as a constant.

CT << New Column( "Prog_", Character );
For Each Row( :Prog_ = If( :Assign Month <= 15Jun2016, "A", "B" ) );

Re: Populating a new column using a conditional formula

dfusco923 — Tue, 29 Dec 2020 03:40:19 GMT

Hello Mark - Thanks for your help with this script!

Dan F.

Re: Populating a new column using a conditional formula

dfusco923 — Tue, 29 Dec 2020 03:43:18 GMT

Thank you for your help with this script!

Dan F.