clean date data

eliyahu100

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

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

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

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:

What is wrong with this script?

TIA, Eli

Mark_Bailey

Maybe try this simpler form.

 

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