Beast mode to extract the date based on a pattern
Hi All,
I am trying to extract the date from a field based on a pattern - If the first four characters of the field description are *SLD then I need to extract the date from the field description as output date else I need to take the date from the field orddate as the the output date..please find the table below:
can you please provide some insights here..
Best Answers
-
Try this to deal with the additional scenarios
DATE( CASE WHEN LEFT(`description`,5) = '*SLD ' THEN IFNULL(TRY_CAST(SPLIT_PART(`description`,' ',2) AS DATE),`orddate`) WHEN LEFT(`description`,4) = '*SLD' THEN IFNULL(TRY_CAST(SPLIT_PART(REPLACE(`description`,'*SLD',''),' ',1) AS DATE),`orddate`) ELSE `orddate` END)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Is your date always in the format of MM/DD/YY? The following regular expression will handle MM/DD/YY and MM/DD/YYYY formats.
I'd recommend doing this in a Magic ETL as it'll reduce the processing needed when displaying your data and not have to calculate it every time you load your cards. You can use a formula tile and a regular expression to extract the date out of your string. You can then compare the extracted version to the original version - if they're the same the regex failed and then you can use the orddate:
extracted_date
REGEXP_REPLACE(`description`, '^.*(\d{1,2}\/\d{1,2}\/(\d{2})|(\d{4})).*$', '$1')
date
IF `extracted_date` = `description` THEN `orddate` ELSE `extracted_date` END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Based on your sample data, this logic should work to extract the date from the description field when it starts with *SLD
DATE(CASE WHEN LEFT(`description`,4) = '*SLD' THEN SPLIT_PART(`description`,' ',2) ELSE `orddate` END)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Hi @MarkSnodgrass : Thank you so much for your response!...I have also cases when data is like
*SLD5/17/22 jilo ( here there is no space between SLD and the date )
Also does this formula append 2022....
In some cases the field has data like.. *SLD owell23451 In this case I should consider only the orddate...Is this possible..
0 -
@ozarkram if your data has no consistency or pattern as to where the date is going to be, then it is going to be extremely difficult to construct the correct statement to extract it. If you can identify all the possibilities, then you might have a chance.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
The other two possibilities are only *SLD5/17/22 jilo (no space after SLD ) and then no date *SLD owell23451 ( here I should consider the ord date)
0 -
Try this to deal with the additional scenarios
DATE( CASE WHEN LEFT(`description`,5) = '*SLD ' THEN IFNULL(TRY_CAST(SPLIT_PART(`description`,' ',2) AS DATE),`orddate`) WHEN LEFT(`description`,4) = '*SLD' THEN IFNULL(TRY_CAST(SPLIT_PART(REPLACE(`description`,'*SLD',''),' ',1) AS DATE),`orddate`) ELSE `orddate` END)
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Is your date always in the format of MM/DD/YY? The following regular expression will handle MM/DD/YY and MM/DD/YYYY formats.
I'd recommend doing this in a Magic ETL as it'll reduce the processing needed when displaying your data and not have to calculate it every time you load your cards. You can use a formula tile and a regular expression to extract the date out of your string. You can then compare the extracted version to the original version - if they're the same the regex failed and then you can use the orddate:
extracted_date
REGEXP_REPLACE(`description`, '^.*(\d{1,2}\/\d{1,2}\/(\d{2})|(\d{4})).*$', '$1')
date
IF `extracted_date` = `description` THEN `orddate` ELSE `extracted_date` END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you so much @MarkSnodgrass and @GrantSmith ...Really appreciate your help!
0 -
@GrantSmith : the below function that you shared is not looking for *SLD at all.. Can you please let me know
REGEXP_REPLACE(`description`, '^.*(\d{1,2}\/\d{1,2}\/(\d{2})|(\d{4})).*$', '$1')
0 -
@MarkSnodgrass : I am getting the following error when I try to do the code below:
Error : For 'isnull', types date and varchar are inconsistent
Do you have any suggestions
- DATE(
- CASE WHEN LEFT(`description`,5) = '*SLD ' THEN
- IFNULL(TRY_CAST(SPLIT_PART(`description`,' ',2) AS DATE),`orddate`)
- WHEN LEFT(`description`,4) = '*SLD' THEN
- IFNULL(TRY_CAST(SPLIT_PART(REPLACE(`description`,'*SLD',''),' ',1) AS DATE),`orddate`)
- ELSE
- `orddate`
- END)
0 -
@ozarkram I would double check your formula because what I wrote doesn't have "isnull" in it, it has "ifnull".
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
Hi @MarkSnodgrass :Yes I have checked the same..I have used only ifnull
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 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