Best way to solve this problem

I am building some logic that will take the earliest date of 4 dates and write it to the target field when a condition is met. This is using the Unix Timestamp so we can assume the smaller number is the earliest date. This seems simple - drop the Min function on the canvas and connect the four dates. However, this does not work when one or more of the for input values is null.

What is the most straightforward method of handling the null and end up with an output of the earliest date (or smallest unix timestamp value?

I know I can add 4 calculation fields to the canvas then go to the min function then go to the calculation that does the date replacement if needed. However this really makes the canvas more complex than it already is for this conversion.

I had thought to try a replace however there does not seem to be a way to pick up the null. It just passes the null on through.

It would be handy to have a null function (Null Replace) that would replace a null with a value (or a space / empty string) in this case. If I could replace the nulls with '9999999999999' then they would always sort last in the min sort I need here.

Any thoughts on this?

It would be handy to have a null function (Null Replace) that would replace a null with a value (or a space / empty string) in this case. If I could replace the nulls with '9999999999999' then they would always sort last in the min sort I need here.

Eventually we will add it as well

You can use validator plus Is null function as alternative

Peter

The problem with handling it at the validator level is that I need to have the null passed through in other fields and calculations in the same transformation.

Well you can do calculations in validation as well

Mike

The approach I am taking now is this in the source SQL statement. I create a second field with this logic for each date field.

IFNULL(UNIX_TIMESTAMP(orders.RO_DATE), 9999999999) AS RO_DATE_IFNULL

Then I can hit either the raw date field or the IFNULL version in any calculations that are needed elsewhere.