Finding "earliest" date when one of them might be NULL

Jeffsnake

I have a dataset with a key field (loan number) and a series of dates.  Within the ETL, I have to calculate several "Earliest Of" dates.

 

So if the dataset has the following fields:

Loan Number

Date A

Date B

Date C

Date D

Date E

 

I might need to create a new field called "Earliest of Date A and B".  I am able to use the Collapse and Group by shapes to achieve this. (Thanks @MarkSnodgrass  - https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Best-way-to-return-earliest-of-multiple-date-fields-in-Magic-ETL/m-p/45604#M7699)

 

But it only works when all the dates are actually populated with a real date.

 

If "Date A" has a date, but "Date B" is null... it is returning the null value as the "Earliest of Date A and B".

 

I either need to:

 

1. figure out how to make the Group by Shape smart enough to "ignore" the null dates in the compare (doesn't seem possible)

 

or

 

2. have a preceeding step that goes through the data and for every date that is null, replace it with 12/31/2299 or some other "real" date that will prevent it from being selected as the earliest.  This seems more likely, but I can't figure out a good way to do it.

 

Any ideas?

 

thanks,
Jeff H.

DDalt

Hey @Jeffsnake 

 

I think you should be able to do option 2 in ETL. Here is a screenshot of the tiles you can use:

 

 

The basic logic there is to set your Date column as Text so that you can then Replace Null values with a "dummy date" like 2299-12-31. Then you append those back to all rows where there is already a date, set the new column with normal and dummy dates back to the "Date" type and then that should give you an output data set with no more null values in your date column. From there, you should be able to use beast mode to create filters on the data set as you see fit!