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:
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)
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.
- 10.6K All Categories
- 13 Getting Started in the Community
- 29 Beastmode & Analytics
- 2.1K Data Platform & Data Science
- 59 Domo Everywhere
- 2.7K Charting
- 2.4K Ideas Exchange
- 1.3K Connectors
- 362 Workbench
- 300 Use Cases & Best Practices
- 499 APIs
- 118 Apps
- 48 News
- 753 Onboarding
- 1.1K 日本支部
- 4 Private Company Board