Charting

Charting

Beast Mode: Extract a string from a column based on a custom character is starting from right

Contributor

I have some data we are pulling in that has a column full of text. The very last part of that string is a part number (variable length) that is always preceded by a dash. I'm wanting to split the column from the right at that dash. How would you do this with Beast Mode?

Examples: (the part number is shown in bold)

123-8473/JHEW-JJDJJD-SQDQO120

89873-98u92-JLKJD@HUN-EMT34

OIUE-19837-LKJLILJ&18347-KLN89

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • Coach
    Answer ✓

    Hi @swagner

    Alternatively you can utilize a regular expression in a Magic ETL Replace Text tile (not exactly what you asked for but wanted to share just in case you wanted to go this route):

    1. ^.*-([^-]+)$

    This will handle any number of dashes in your string.

    Copy and paste this into your Magic ETL:

    1. {"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"d7a9e1bd-ebbb-4b13-ad2b-75fd0e98fa1b","type":"ReplaceString","gui":{"x":210,"y":408},"dependsOn":["83116b2f-90d1-4b7b-ad6b-83213ce84fa4"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"Part","useRegex":true,"replaceString":"^.*-([^-]+)$","replaceByString":"$1","wholeWord":false,"caseSensitive":false}]}]}
    2.  
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • @swagner Assuming you have always 3 dashes in there (as shown in your examples) you can use SPLIT_PART to get the string after the 3rd dash, which is the 4th part.

    SPLIT_PART(`String`,'-',4)

    Hope this helps.

    **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.
  • Coach
    Answer ✓

    Hi @swagner

    Alternatively you can utilize a regular expression in a Magic ETL Replace Text tile (not exactly what you asked for but wanted to share just in case you wanted to go this route):

    1. ^.*-([^-]+)$

    This will handle any number of dashes in your string.

    Copy and paste this into your Magic ETL:

    1. {"contentType":"domo/dataflow-actions","data":[{"name":"Replace Text","id":"d7a9e1bd-ebbb-4b13-ad2b-75fd0e98fa1b","type":"ReplaceString","gui":{"x":210,"y":408},"dependsOn":["83116b2f-90d1-4b7b-ad6b-83213ce84fa4"],"removeByDefault":false,"notes":[],"fields":[{"inStreamName":"Part","useRegex":true,"replaceString":"^.*-([^-]+)$","replaceByString":"$1","wholeWord":false,"caseSensitive":false}]}]}
    2.  
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith I knew you were going to suggest regex! 😂

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

    @MarkSnodgrass thanks for the reply, unfortunately the dash appears a variable number of times (not just 3). I'll definitely keep that in mind for future. @GrantSmith not sure I understand, but will dig in now to wrap my head around it.

  • @swagner

    Since beast modes don't support regular expressions you'll need to do it within an ETL. The JSON code I posted above will give you the starting point for a Replace Text tile in a Magic ETL 2.0 dataflow. You can copy that second code block and then just paste it when in the ETL it'll put that tile in for you with the regex formula already applied.

    The regular expression is simply stating "Give me everything after the last - that isn't a dash" if that help clarifies things.

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

    @GrantSmith that is incredible! THANK YOU!!!!

  • @swagner great use of regex by @GrantSmith as usual.

    Just to see if I could, I came up with a way to do it in Beast Mode:

    SPLIT_PART(`String`,'-',LENGTH(`String`) - LENGTH(REPLACE(`String`,'-','')) + 1)

    To dynamically determine the number of dashes in the string, you can utilize the LENGTH and REPLACE functions. I am taking the length of the field (string) and then subtracting it from the length of the field after I remove all the dashes. This tells me how many dashes are in the string. I add 1 to the result so that I get the part after the last dash to use for the SPLIT_PART function

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

    @MarkSnodgrass that's awesome! Much easier for me to "decode" and use for other things later. Thanks for providing that soluion as well. Hopefully this will help others in the future as well.

  • 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"
  • Contributor

    @jaeW_at_Onyx Thanks!

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In