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

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

fuscod — Fri, 09 Jun 2023 23:33:30 GMT

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!

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

txnelson — Tue, 28 Jul 2020 05:54:28 GMT

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

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

fuscod — Wed, 29 Jul 2020 12:48:14 GMT

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!

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

txnelson — Tue, 28 Jul 2020 22:23:51 GMT

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

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

fuscod — Wed, 29 Jul 2020 12:48:54 GMT

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",
             Charcter,
             Nominal,
             format (Best, 12),
             Formula(Substr(:YEAR, 5, 7))
);
CT2 << Run formulas;
"Mon-Yr" << delete formula;

YOUR SCRIPT　

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

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

txnelson — Tue, 28 Jul 2020 22:31:13 GMT

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

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

fuscod — Wed, 29 Jul 2020 12:49:30 GMT

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",
     Character,
     Nominal,
   Formula(Substr(:YEAR, 5, 7))
     );
CT << Run formulas;
"Mon-Yr" << delete formula;

YOUR SCRIPT:

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

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

txnelson — Wed, 29 Jul 2020 01:30:00 GMT

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;

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

fuscod — Wed, 29 Jul 2020 03:09:44 GMT

Thank Jim - It worked perfectly.