topic Re: Difficulties Formatting Time Column in Discussions

Difficulties Formatting Time Column

user8421 — Wed, 19 Feb 2020 12:42:29 GMT

Hi everyone!

I have a CSV of dates and times that I upload into JMP. In order to create a single column with date/time I need to convert the data type and format of the column that contains the date.

When I use the script:

Column("Actual Harvest Time") << Data Type(Numeric); << Format("h:m")

It totally clears the column of any data.

I also learned that when I manually change the column modeling type to Numeric and hit apply, that is where the data gets cleared. If I manually change the column to Numeric AND the format to "h:m" at the same time and hit apply, the format is changed successfully.

I'm looking for a way to automate the formatting of the time column without losing any data. Is there a way to do this without it clearing all my data?

I attached a screengrab of the manual column formatting changes for reference.

(view in My Videos)

Re: Difficulties Formatting Time Column

txnelson — Wed, 19 Feb 2020 02:08:52 GMT

I replicated your findings, and the findings are the same for both JMP 14 and JMP 15. I guess you will have to create a new column in JSL to do what you want.

names default to Here(1);
dt = New Table( "file",
	New Column( "a", character, values({"11:02"})));
	
	
dt<<new column("new time", numeric,informat("h:m"),format("h:m"),continuous,	
formula( informat(:a,"h:m")));

Re: Difficulties Formatting Time Column

Mark_Bailey — Wed, 19 Feb 2020 10:55:26 GMT

Yes, if you change the data type first, then you lose the values. You must simultaneously provide the format of the date and time for the original character string version. Then JMP can convert the character string to the corresponding numeric value.

Here is an example from the Scripting Index entry for the data column message << Data Type():

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << New Column( "Time",
	"Character",
	"Nominal",
	Set Values( {"13:32", "20:10", "20:12", "14:56"} )
);
Wait( 2 );
dt:Time << Data Type( Numeric, Format( "h:m", 12 ), Input Format( "h:m" ) );
dt:Time << Modeling Type( "Continuous" );