cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Check out the JMP® Marketplace featured Capability Explorer add-in
Choose Language Hide Translation Bar
UserID16644
Level V

Change column format

Hi all, 

I am opening a csv file with dates (0000-00-00) having a default column data type character. However, whenever I tried to update the column with a formula from another column with a date value, I cannot change the format into date. My code looks something like this

dt = Open ("C:\Users\table1.jmp");
dt2 = Open ("C:\Users\table1.jmp");

dt2 << Update( With( dt),
	Match Columns( :idFin = :idFin ),
	Add Columns from Update Table( :Start_Date, :End_Date )
);

dt2:Name ("Actual Start Date") << Data Type( Numeric,  Format( "y/m/d", 19 ));
dt2:Name ("Actual Start Date") << Set Formula( :Start_Date );

After having the formula (:Start_Date) on my Actual Start Date column, it doesn't change to y/m/d format but instead it has the Best format. Please help

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Change column format

A few issues

  1. You can not reset the Datatype etc. when the column is has a formula that sets the value for the column.
  2. I don't understand what the purpose of running the Update Platform when it was updating a data table with the same data table.
  3. I don't understand what dt9090 is.
  4. You need to create the column using the formula you specified(and then remove the formula) before changing the data type and format.

Below is a much simplified script that creates a simple example data table that has your indicated original date structure, and then adds a new column.  It then deletes te formula and then changes the data type and sets the new format.

names default to here( 1 );
dt = New Table( "Example",
	New Column( "Start_Date", character, set values( {"2023-03-22", "2022-12-04"} ) )
);

dt << New Column( "Actual Start Date", character, Formula( :Start_Date ) );
dt:Actual Start Date << delete formula;
dt:Actual Start Date << Data Type( Numeric ) << modeling type( continuous );
dt:Actual Start Date << Format( "yyyy-mm-dd", 19 );
Jim

View solution in original post

1 REPLY 1
txnelson
Super User

Re: Change column format

A few issues

  1. You can not reset the Datatype etc. when the column is has a formula that sets the value for the column.
  2. I don't understand what the purpose of running the Update Platform when it was updating a data table with the same data table.
  3. I don't understand what dt9090 is.
  4. You need to create the column using the formula you specified(and then remove the formula) before changing the data type and format.

Below is a much simplified script that creates a simple example data table that has your indicated original date structure, and then adds a new column.  It then deletes te formula and then changes the data type and sets the new format.

names default to here( 1 );
dt = New Table( "Example",
	New Column( "Start_Date", character, set values( {"2023-03-22", "2022-12-04"} ) )
);

dt << New Column( "Actual Start Date", character, Formula( :Start_Date ) );
dt:Actual Start Date << delete formula;
dt:Actual Start Date << Data Type( Numeric ) << modeling type( continuous );
dt:Actual Start Date << Format( "yyyy-mm-dd", 19 );
Jim