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
eliyahu100
Level III

clean date data

I have a date column that looks like this after importing it from Excel:

eliyahu100_0-1593630401942.png

It's Properties are - Data Type("Character"), Modeling Type("Nominal") .

I want to turn into proper dates so it looks like this:

eliyahu100_1-1593630640648.png

When I replace the dots into / using the edit menue  --> replace and then change the properies to Numeric and Continuous, and then the format into d/m/y every thing works fine.

So I wrote the following code to do this:

	nr = nrows(dt);
	col = Column( "DOB" );
	for (k = 1, k <= nr, k++,
        col[k] = substitute(col[k], ".", "/");    // Replace dot with /
		);		
	:"DOB" << Add Column Properties(Data Type("Numeric"), Modeling Type("Continuous"), Format("d/m/y", 12), Input Format("d/m/y"), Set Selected);

Unfortunatly this does not work and i get the following:

eliyahu100_2-1593631014297.png

What is wrong with this script?

TIA, Eli

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: clean date data

Maybe try this simpler form.

 

For Each Row(
        :DOB = Substitute( :DOB, ".", "/");
);		
:"DOB" << Data Type( "Numeric", Format( "d/m/y" ) ) <<  Modeling Type( "Continuous" );
Learn it once, use it forever!

View solution in original post

1 REPLY 1
Highlighted

Re: clean date data

Maybe try this simpler form.

 

For Each Row(
        :DOB = Substitute( :DOB, ".", "/");
);		
:"DOB" << Data Type( "Numeric", Format( "d/m/y" ) ) <<  Modeling Type( "Continuous" );
Learn it once, use it forever!

View solution in original post

Article Labels