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

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?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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
    GrantSmith Coach
    Answer ✓

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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**