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!
Advanced Statistical ModelingAutomation and ScriptingMass CustomizationRe: 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.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;
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;
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" ) )
);
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" ) )
);
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" ) )
);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!
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
)
)
