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

Jeffsnake
Jeffsnake Contributor
in Magic ETL

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.

Tagged:

Best Answer

  • DDalt
    DDalt Member
    Answer ✓

    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!

Answers

  • DDalt
    DDalt Member
    Answer ✓

    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!

  • DDalt
    DDalt Member

    @Jeffsnake Did that solution work for you?

  • Jeffsnake
    Jeffsnake Contributor

    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.

  • DDalt
    DDalt Member

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

Categories