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-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.
Best 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:
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!
1
Answers
-
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!
1 -
@Jeffsnake Did that solution work for you?
0 -
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.0 -
Nice... if you have that ability then sometimes that is just the easiest way!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive