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

Reply
Highlighted
Brown Belt

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

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...)

 

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.

Tags (1)

Accepted Solutions
Green Belt

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

Hey @Jeffsnake 

 

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

 

Screenshot_3.png

 

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!

View solution in original post


All Replies
Green Belt

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

Hey @Jeffsnake 

 

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

 

Screenshot_3.png

 

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!

View solution in original post

Green Belt

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

@Jeffsnake Did that solution work for you?

Brown Belt

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

I think it would have, but I had so many dates/comparisons, I would have had to branch like 25 times in the ETL... so I ended up going back to the source (SQL Server) and did the date comparisons in SQL.

 

thanks,
Jeff H.

Green Belt

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

Nice... if you have that ability then sometimes that is just the easiest way!

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!