Magic ETL Trim left of decimal

Canioagain

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

Canioagain

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

 

codysirwin

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.

Canioagain

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

 

codysirwin

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.