Formula failing to convert value from a type string to a type date

Golfoholic · 2021-12-14T21:33:22+00:00

There was an error rendering this rich post.

I think I need some help with the order of operations on this. I have recursive dataflow for Yelp data. I am trying to create a 'metric date' column by 1st, Splitting the ' FILE_NAME' column date example 'security_public_storage_account_time_range_report_11_1_2021_to_11_30_2021.xls

Then in the next Tile I am using the Add formula tile to:

But you can see the error I'm receiving from it not being able to change it from a string to a Date. I am unsure of the order to go about doing this. Can it be handled with a formula?

Find more posts tagged with

Dataset

Magic ETL

Data Flows

Accepted answers

GrantSmith

One minor issue with the prior beast mode, it's short circuit evaluating to only pull in the 2nd digit in the months. This update version should resolve that issue:

REGEXP_REPLACE(`_FILE_NAME_`, '^.*_(\d{1,2})_(\d{1,2})_(\d{4})_to_(\d{1,2})_(\d{1,2})_(\d{4}).*$', '$1/$2/$3')

REGEXP_REPLACE(`_FILE_NAME_`, '^.*_(\d{1,2})_(\d{1,2})_(\d{4})_to_(\d{1,2})_(\d{1,2})_(\d{4}).*$', '$4/$5/$6')

All comments

MarkSnodgrass

If you are trying to get the date that is just after the "_ to _" you can use this in the formula tile to get it:

DATE(REPLACE(LEFT(split_part(`metricdate`,'_to_',2),10),'_','/'))

If you are trying to get the date that is before the " _ to _ " that will be slightly different.

GrantSmith

It's splitting the first part of your filename 'security/public/storage/account/time/range/report/11/1/2021' as the date. This of course isn't a format that Domo can understand for a date. For more complex cases like yours I recommend using a regular expression in a formula tile:

Start Date:

REGEXP_REPLACE(`_FILE_NAME_`, '^.*(\d{1,2})_(\d{1,2})_(\d{4})_to_(\d{1,2})_(\d{1,2})_(\d{4}).*$', '$1/$2/$3')

End Date:

REGEXP_REPLACE(`_FILE_NAME_`, '^.*(\d{1,2})_(\d{1,2})_(\d{4})_to_(\d{1,2})_(\d{1,2})_(\d{4}).*$', '$4/$5/$6')

Screen Shot 2021-12-14 at 4.43.09 PM.png

Breaking it down: ^.* means to match anything (\d{1,2}) matches 1-2 digits into a group. It's grabbing the 6 different date segments (start month, start day, start year, end month, end day, end year) and storing them in variables $1-$6. .*$ says to match the rest of the string. So it's matching the entire string then replacing it with the values we captured in the variables. The start date uses variables $1-$3 and end date are in $4-$6 - the number of the group defined left to right by the parenthesis.

Golfoholic

@GrantSmith just looking at what you have above, it makes sense and is doing what I am looking. Only thing that would need to be adjusted is that in this example it is for the entire month of November. So, that start date would be 11/1/2021.

GrantSmith

One minor issue with the prior beast mode, it's short circuit evaluating to only pull in the 2nd digit in the months. This update version should resolve that issue:

REGEXP_REPLACE(`_FILE_NAME_`, '^.*_(\d{1,2})_(\d{1,2})_(\d{4})_to_(\d{1,2})_(\d{1,2})_(\d{4}).*$', '$1/$2/$3')

REGEXP_REPLACE(`_FILE_NAME_`, '^.*_(\d{1,2})_(\d{1,2})_(\d{4})_to_(\d{1,2})_(\d{1,2})_(\d{4}).*$', '$4/$5/$6')