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

psmith
psmith Member
edited March 2023 in Datasets

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
    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:


    **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.
  • GrantSmith
    GrantSmith Coach
    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

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

    **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.
  • MarkSnodgrass
    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:


    **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.
  • GrantSmith
    GrantSmith Coach
    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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 ❀️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • 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)

  • contact your CSM / Domo Account Representative or support@domo.com 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 ❀️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thanks @jaeW_at_Onyx . We actually have a meeting tomorrow with our account rep so I'll bring it up.

  • SM17
    SM17 Member
    edited July 2023

    Thanks! this has been very helpful to me… say we already converted both to integers for minutes and seconds, how do we sum both and convert to hours?