Replacing null values with values from a different column

Good Morning!

I am merging two tables using a Union tool, and after the merge condensing the data a bit. Each of the tables have duplicate information, but with different column names (ie OPENING_DTE, Formatted Open Date, and Open Date - all to represent the opening date of a building). I have a handful of other columns that I can combine easily with the formula tool, but for some reason the date column is giving me problems. I tried changing the date types around between Date, DateTime and V_String and cannot get the values to copy. I'm using the below expression in the formula tool:

IF [OPENING_DTE] = Null() THEN [Open Date] ELSE [OPENING_DTE] ENDIF

Trying to copy the values from "Open Date" to the "OPENING_DTE" column. Currently the "Open Date" column is a V_String, and the OPENING_DTE column is in DateTime

	OPENING_DTE	Open Date
Building A	6/3/2011
Building B	2/5/1985
Building C	(Null)	8/6/2004
Building D	(Null)	5/21/2018

Thank you!

Developer

Tips and Tricks

Date Time

Expression

Accepted answers

apathetichell

select doesn't work on date conversion unless something is formatted perfectly going in (ie. "YYYY-mm-dd"). You can add a formula tool and use datetimeparse([DATE - or whatever your field name is],"%m/%d/%Y") assuming that your dates are formatted as 6/10/2021. In the alternative if you have lots of fields or love the specific column names your best way to go is a multi-field formula.

Here youl'll cick on the columns you want. You'll click to change data type (to date), you uncheck change field name and in the formula bar you'll type

datetimeparse([_currentfield_],"%m/%d/%Y")

note, again this is set up to work on dates formatted as 6/10/2021. If you need customization of dates - the date/time tool is your best introduction to date/time conversion. You can create a new datetime field using it and then rename and drop your other field using a select tool.

If these don't work - post some data samples and someone will get you on firm footing.

All comments

apathetichell

As an alternative may I suggest the "Manually Configure Fields" option on union? Its in the dropdown with config by name. That will allow you to match fields with different names.

ijurgensen

Oh wow, very cool. I tried doing this, and it sprouted up about a half dozen other errors downstream. I'd like to use this in the future, but if there's a way to fix this isolated problem without changing the Union settings, I'd prefer that.

apathetichell

My bad - just noticed this part:

Trying to copy the values from "Open Date" to the "OPENING_DTE" column. Currently the "Open Date" column is a V_String, and the OPENING_DTE column is in DateTime

You need to make a choice of which one you want date time or string for your new column. You can convert them back and forth with tostring() to date() but on the date conversion you need to make sure your formatting (which looks like ("%m/%d/%Y")) to me is used - otherwise you can't place information from both [open date] and [Opening_DTE] (whichever isn't null()) in the same column.

ijurgensen

I tried using the Select tool to change the data type of all the incoming data to "Date", but it still didn't work. Do I need to use another tool to make this conversion?

apathetichell

Here youl'll cick on the columns you want. You'll click to change data type (to date), you uncheck change field name and in the formula bar you'll type

datetimeparse([_currentfield_],"%m/%d/%Y")

If these don't work - post some data samples and someone will get you on firm footing.

ijurgensen

Perfect. The DateTime parse tool did the trick. I reformatted the dates coming out of the one problematic data stream, and the output of the final workflow matches exactly. THANK YOU!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703