nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Create a numeric, ordinal, best (12), format = ("m\y" ) "Mon-Yr" column from a pre-existing character column with "Mon-Yr" = "01/2020"

fuscod

I have a pre-existing character "Year" column for example "K001/2020", "K002/2020", "K003/2020", etc.

Using script I'm able to create a new character "Mon-Yr" column = "01/2020", "02/2020", "03/2020", etc. [ I used the formula substring ("Year", <4,7>) to do this. ]

What JMP script can I use to convert the character "01/2020", "02/2020", "03/2020", etc. to numeric, ordinal, best (12), format ("m/y")?

Thanks in advance for your help!

Accepted answers

txnelson

I repeat from my previous response

".....because your column name of ":Mon-Yr" will be interpreted by JMP as a column named Mon and then sub tract off -Yr."

JMP will do the same thing with your << delete formula;

Your code needs to be changed from:

:Mon-Yr  <<  delete formula;

to:

:Name("Mon-Yr") << delete formula;

All comments

txnelson

Here is a little script that creates 3 columns. Column 1 is Char Date, using your "02/2020" form. The second column is a formula column that converts the first column into a JMP Date value, with a "m/y" format for display by tearing apart the character column and rebuilding it. The third uses the Informat() function to convert the column

New Table( "example",
	Add Rows( 1 ),
	New Column( "Char Date", Character, "Nominal", Set Values( {"02/2020"} ) ),
	New Column( "JMP Date",
		Numeric,
		"Nominal",
		Format( "m/y", 12 ),
		Input Format( "m/y" ),
		Formula(
			Date MDY(
				Num( Word( 1, :Char Date, "/" ) ),
				1,
				Num( Word( 2, :Char Date, "/" ) )
			)
		)
	),
	New Column( "JMP Date Using Informat",
		Numeric,
		"Continuous",
		Format( "m/y", 12 ),
		Input Format( "m/y" ),
		Formula( Informat( :Char Date, "m/y" ) ),
		Set Selected
	)
)

fuscod

thanks for the above; I'm at my computer now and can better explain what I'm trying to do.

I have a pre-existing "YEAR" columnt [Character, Nominal], example of the "YEAR" data:

K9/001/2019
K9/001/2020
K9/002/2019
K9/002/2020
K9/003/2019

I have scripted a new column "Mon-Yr"

CT2 << New Column("Mon-Yr", Charcter, Nominal, format (Best, 12),
Formula(Substr(:YEAR, 5, 7))
);
CT2 << Run formulas;
"Mon-Yr" << delete formula;

That results in a new column, "Mon-Yr", [Character, Nominal]. Example of "Mon-Yr" data:

01/2019
01/2020
02/2019
02/2020
03/2019

So at this point I'd like to create a new column "Mon-Yr_2" [Numeric, Nominal, format ("m/y"]. Can you help to show me how to script this?

Thanks!

txnelson

Taking from my example, with minor modifications

CT2 << New Column( "Mon-Yr_2",
		Numeric,
		"Nominal",
		Format( "m/y", 12 ),
		Input Format( "m/y" ),
		Formula( Informat( :Name("Mon-Yr"), "m/y" ) )
	);

fuscod

Jim Thanks Again for your help with this.

Below is the combined script I'm running; per your code it creates a new column "Mon-Yr_2" but the column no data . . . the data is missing "."

I'm also getting some error messages I don't understand. I attached a sample of the starting data table and the log.

MY SCRIPT

CT2 << New Column("Mon-Yr",
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Charcter,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Nominal,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;format (Best, 12),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;Formula(Substr(:YEAR, 5, 7))
);
CT2 << Run formulas;
"Mon-Yr" << delete formula;

YOUR SCRIPT　

CT2 << New Column( "Mon-Yr_2",
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Numeric,
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Nominal",
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Format( "m/y", 12 ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Input Format( "m/y" ),
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Formula( Informat( :Mon-Yr, "m/y" ) )
);

Sample DS - After running script to add new column.jmp

Log.jsl

Sample DS - Before running script to add new column.jmp

txnelson

My error. I wrote and tested my code on a data table that had a different name for the character input variable column, and then when I saw what your actual name was, I just cut an pasted it into my formula. That was a mistake, because your column name of ":Mon-Yr" will be interpreted by JMP as a column named Mon and then sub tract off -Yr. The following is what needs to be changed.......I have gone back and changed my last response so future readers will get the correct code.

CT2 << New Column( "Mon-Yr_2",
                     Numeric,
            "Nominal",
            Format( "m/y", 12 ),
            Input Format( "m/y" ),
            Formula( Informat( :Name("Mon-Yr"), "m/y" ) )
);

fuscod

The script runs OK, but I keep getting an "error" message in the log for the script that I wrote (see attached log). I think it is related to the "Mon-Yr" column name.

Thanks for your help!

MY SCRIPT:

CT << New Column("Mon-Yr",
&nbsp;&nbsp;&nbsp;&nbsp; Character,
&nbsp;&nbsp;&nbsp;&nbsp; Nominal,
&nbsp;&nbsp;&nbsp;Formula(Substr(:YEAR, 5, 7))
&nbsp;&nbsp;&nbsp;&nbsp; );
CT << Run formulas;
"Mon-Yr" << delete formula;

YOUR SCRIPT:

CT << New Column( "Mon-Yr_2",
&nbsp;&nbsp;&nbsp;&nbsp; Numeric,
&nbsp;&nbsp;&nbsp; "Nominal",
&nbsp;&nbsp;&nbsp; Format( "m/y", 12 ),
&nbsp;&nbsp;&nbsp;&nbsp;Input Format( "m/y" ),
&nbsp;&nbsp;&nbsp;&nbsp;Formula( Informat( :Name("Mon-Yr"), "m/y" ) )
);
CT << Run formulas;
"Mon-Yr_2" << delete formula;

Log_2.jsl

txnelson

I repeat from my previous response

".....because your column name of ":Mon-Yr" will be interpreted by JMP as a column named Mon and then sub tract off -Yr."

JMP will do the same thing with your << delete formula;

Your code needs to be changed from:

:Mon-Yr  <<  delete formula;

to:

:Name("Mon-Yr") << delete formula;