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


  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted 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}]}]}
    

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.

  • 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

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

  • psmith
    psmith Florida ⚪️

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