Trying to Split a Text Field with Time data (HH:MM:SS) into usable data

psmith
psmith Florida ⚪️

I am trying to split up a column using the ETL. The field is formatted as TEXT and has both HH:MM:SS data and MM:SS data (not uniform). I need to convert it to a uniform format so I can break it down into Hours, minutes and seconds of time (End result I am going to add it all up to seconds). After splitting I will convert each field to integer format. Any help to making the data uniform?

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    @psmith it turned out to be not too difficult to create samples for you in both versions of Magic ETL. Here is what they look like

    Version 1

    In the 1st string operations tile, extract the seconds by choosing Right 2. Extract the minutes by choosing right 5. (You're initially grabbing more than the minutes, but we will take care of that in the next tile.)

    In the 2nd string operations tile, extract the minutes by choosing Left 2.

    In the Replace Text tile, choose the minutes field you just created, enter : (colon) for the term to search for and leave the 3rd box blank in what to replace it with.

    In the Set Column Type tile, choose your seconds and minutes fields you created and set them to type of integer.


    Version 2

    Use the Add Formula Tile and use the following formulas:

    Seconds: RIGHT(`Time`,2)

    Minutes: REPLACE(LEFT(RIGHT(`Time`,5),2),':','')

    Use the Alter Columns tile to change the data type to integer for each of those. Results should look like this based on my sample data:





    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷
    Answer ✓

    Hi @psmith

    Regular expressions are one way to solve your problem. Assuming your text is ONLY HH:M:SS OR MM:SS you can utilize a replace text tile (Magic ETL 1.0 or 2.0) to pad your MM:SS formatted strings to be 00:MM:SS

    Here's the regular expression you can copy and past:

    ^(\d{2}):(\d{2})$
    

    And here's the Replacement string:

    00:$1:$2
    


    Alternatively you can copy and paste this code into your Magic ETL dataflow and it should* populate the tile automatically for you. You'll just need to select the correct column storing your string and attach the input.

    {"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"a75b422e-7cb5-4c7f-a166-932ed27bd321","type":"ReplaceString","gui":{"x":420,"y":168},"dependsOn":["d0858ff2-48dc-4f30-8151-ba3426ba302a"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"MMSS","useRegex":true,"replaceString":"^(\\d{2}):(\\d{2})$","replaceByString":"00:$1:$2","wholeWord":false,"caseSensitive":false}]}]}
    


    **Was this post helpful? Click the heart icon**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    @psmith are you using the new Magic ETL 2.0? If so, the solution will be simpler. If not, you can still accomplish this, but will require a few more steps. Let me know so I can provide the proper solution.




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @GrantSmith and @MarkSnodgrass i love both of your solutions (especially the regex!)


    if it were me, I would have done something like,


    CASE

    WHEN <text contains 3 colons> then text

    WHEN <text contains 2 colons> then concat 00:

    END


    then use TIME_To_SEC


    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • psmith
    psmith Florida ⚪️

    Thanks everyone! I used a combination of both answers 1 & 2 to complete what I wanted to do. We seem to be on Magic ETL 1.0 right now (Just started in Domo at our workplace). Thanks @MarkSnodgrass @GrantSmith

    There is no formula button so thats why I assume we are on 1.0 (no place to use case)

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    contact your CSM / Domo Account Representative or [email protected] and ask them to push the Magic 2.0 beta. it's free and available if you ask for it.


    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • psmith
    psmith Florida ⚪️

    Thanks @jaeW_at_Onyx . We actually have a meeting tomorrow with our account rep so I'll bring it up.