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

Golfoholic

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?

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')

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')

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')