cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
fuscod
Level I

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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;

 

Jim

View solution in original post

8 REPLIES 8
txnelson
Super User

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

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

 

Jim
fuscod
Level I

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

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
Super User

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

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" ) )
	);
Jim
fuscod
Level I

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

 

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

 

txnelson
Super User

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

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

 

Jim
fuscod
Level I

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

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;

 

 

txnelson
Super User

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

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;

 

Jim
fuscod
Level I

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

Thank Jim - It worked perfectly.