Transforming 6 digit integer to a date when bad values are involved
I have a dataset with chunks of bad data in it. To get a MFG date I pulled the first 6 digits of the serial # to get a "date" in an text format.
if the data is correct it should read out the correct date, but in some cases the data will have 06B378 or other letters or texts that should not be in there. How would I go about excluding the bad data as well as converting the good 6 digit data into a date format?
Let me know your thoughts or how I go about this. I tried to alter columns to integers and inherit bad values as null but it seems to not be catching all bad values.
Thanks
Answers
-
Hi @gbrown ,
So you're going to go have to do 2 steps:
1) Determine which cells are truly numeric
2) Convert the numeric ones to dates
Here are the formulas to do the steps:
1) CASE
WHEN STR_REMOVE_DIGITS(`Date_Temp`)='' THEN 1 END
2) CASE
WHEN `Numeric Format Flag`=1
THEN CAST(CONCAT('20',RIGHT(`Date_Temp`,2),'-',left(`Date_Temp`,2),'-',SUBSTRING(`Date_Temp`,3,2)) as date)
END
Even better! Here's a video of me explaing what all this mumbo jumbo is: https://www.loom.com/share/ebf8d97b0ec94d458b6d09813dc93e2c
Hope that helps,
John Le
More video solutions at https://www.dashboarddudes.com/pantry
John Le
Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more
Signup here:
1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 657 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 775 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 81 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive