Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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
Highlighted
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!

 

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

 

Highlighted
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
Highlighted
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;

 

 

Highlighted
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

Highlighted
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.
Article Labels