Magic ETL Trim left of decimal

Canioagain
Canioagain Contributor
edited March 2023 in Scheduled Reports

I noticed there isn't a ton (easily found) info on the different Regex to use to trim off data.  

 

My use case was I had a file using a numbering system before a product.  So instead of saying Lowe's, Home Depot, Walmart it would sayd 1.Lowes, 2. Home Depot, and 3. Walmart.  I found some regex that drops the period and what's to the left of it.  

 

So here you go. 

Use the Replace Text >>> Click the Gear on Step 2 >>>> Check "Use RegEx" >>> set the expression to  ^[^.]*.  and it will work

domo.png

Tagged:

Best Answers

  • Canioagain
    Canioagain Contributor
    Answer ✓

    I noticed there isn't a ton (easily found) info on the different Regex to use to trim off data.  

     

    My use case was I had a file using Zip codes.  Some of the Zip codes in the .xls have 92056-1234.  Well, when you try to join that data to Domo's Fips to Zips Dimension you have to get rid of the dash and everything after it.  The trick here is you can't use a trim on the last 5 because you only want to trim those data which have the dash in it.  

     

    So here you go. 

    Use the Replace Text >>> Click the Gear on Step 2 >>>> Check "Use RegEx" >>> set the expression to   \-.*$  and it will work

    domo.png

     

  • codysirwin
    codysirwin Domo Employee
    Answer ✓

    Canio - That is a great solution!  REGEX is incredibly powerful, but as you stated, it can be hard to find intuitive solutions unless you've spent a lot of time in the syntax.  Domo's Knowledge Base recommends the Java Tutorial for some deep information on the inner workings of the functionality (https://docs.oracle.com/javase/tutorial/essential/regex/).

     

    I've found that it is also helpful to use some other sites like Regex101 (https://regex101.com/) to build out and experiment with different patterns.


    Cody Irwin
    Manager, Technical Solutions
    **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"

Answers

  • Canioagain
    Canioagain Contributor
    Answer ✓

    I noticed there isn't a ton (easily found) info on the different Regex to use to trim off data.  

     

    My use case was I had a file using Zip codes.  Some of the Zip codes in the .xls have 92056-1234.  Well, when you try to join that data to Domo's Fips to Zips Dimension you have to get rid of the dash and everything after it.  The trick here is you can't use a trim on the last 5 because you only want to trim those data which have the dash in it.  

     

    So here you go. 

    Use the Replace Text >>> Click the Gear on Step 2 >>>> Check "Use RegEx" >>> set the expression to   \-.*$  and it will work

    domo.png

     

  • codysirwin
    codysirwin Domo Employee
    Answer ✓

    Canio - That is a great solution!  REGEX is incredibly powerful, but as you stated, it can be hard to find intuitive solutions unless you've spent a lot of time in the syntax.  Domo's Knowledge Base recommends the Java Tutorial for some deep information on the inner workings of the functionality (https://docs.oracle.com/javase/tutorial/essential/regex/).

     

    I've found that it is also helpful to use some other sites like Regex101 (https://regex101.com/) to build out and experiment with different patterns.


    Cody Irwin
    Manager, Technical Solutions
    **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"
This discussion has been closed.

Categories