Convert Column to numeric continuous Date - using Format Pattern - Bug?

[#1]

Hi,

I have the following input data table, attached input_table.jmp, which is treated by my script, see below or attached after_import_analysis_script.jsl. I want to convert col 1 (character, nominal, input format "<DD>/<MM>/<YY> <hh>:<mm>:<ss>") to Date (numeric, continuous, output format "<DD>.<MM>.<YY> <hh>:<mm>:<ss>"). However if I use my script then the conversion is wrong in the rows from 2656 to 2657,. It converts it instead to "<MM>.<DD>.<YY> <hh>:<mm>:<ss>" from row 2657 on. Therefore the duration between row 2656 to 2657 is a month instead of 10 seconds. Please find the buggy data table attached as bugged.jmp

Any idea what's wrong here?

I am using Windows 10, JMP 18 as well as JMP 18 Pro.

Thanks!

script:

dt = Current Data Table();

// Column configuration
dt << Begin Data Update;
    // Rename and configure columns using column numbers
    Column(dt, 1) << Set Name("Date")
        << Data Type(Numeric)
        << Modeling Type("Continuous")
        << Format("Format Pattern", "<DD>.<MM>.<YY> <hh>:<mm>:<ss>", 17, 0 )
        << Input Format( "Format Pattern", "<DD>/<MM>/<YY> <hh>:<mm>:<ss>", 0  );
    
    Column(dt, 2) << Set Name("minutes")
        << Formula((:Date-:Date[1]) / 60)
        << Format("Best", 12);
    
    Column(dt, 3) << Set Name("Temperature 1 [°C]");
    Column(dt, 4) << Set Name("Temperature 2 [°C]");
    Column(dt, 7) << Set Name("Target Temperature 1 [°C]");
    Column(dt, 8) << Set Name("Target Temperature 2 [°C]");
    
    // Remove unused columns
    dt << Delete Columns({"Col 5", "Col 6"});
dt << End Data Update;

// Set display properties
dt << Set Display Width("Date", 180);
dt << Set Display Width("minutes", 50);
dt << Set Display Width("Temperature 1 [°C]", 50);
dt << Set Display Width("Temperature 2 [°C]", 50);

Graph Builder(
	Size( 1239, 816 ),
	Variables( X( :minutes ), Y( :"Temperature 1 [°C]"n ) ),
	Elements(
		Points( X, Y, Legend( 7 ) ),
		Smoother(
			X,
			Y,
			Legend( 9 ),
			Method( "Local Kernel" ),
			Lambda( 0.000001 ),
			Local Width( 0.1284 )
		)
	)
);

[#2 · Accepted solution]

I think the issue might happen because you are chaining the formatting messages. Change to Numeric will happen before Input format which will happen before format -> JMP will decide the format to use when column is changed to Numeric. You could either re-order them OR send them at the same time, especially the ones related to the data type.

Instead of

    Column(dt, 1) << Set Name("Date")
        << Data Type(Numeric)
        << Modeling Type("Continuous")
        << Input Format( "Format Pattern", "<DD>/<MM>/<YY> <hh>:<mm>:<ss>", 0  )
        << Format("Format Pattern", "<DD>.<MM>.<YY> <hh>:<mm>:<ss>", 17, 0 );

use something like

Column(dt, 1) << Set Data Type(
	Numeric,
	Input Format("Format Pattern", "<DD>/<MM>/<YY> <hh>:<mm>:<ss>", 0),
	Format("Format Pattern", "<DD>.<MM>.<YY> <hh>:<mm>:<ss>", 17, 0)
) << Set Modeling Type("Continuous") << Set Name("Date");

[#3]

Thanks for rapid reply!
I understand the issue and your suggested solutions work.

Thanks!