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
Highlighted
Green Belt

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
Highlighted
Green Belt

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

Highlighted
Green Belt

@Jeffsnake Did that solution work for you?

Highlighted
Brown Belt

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.

Highlighted
Green Belt

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

Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.