Magic ETL Trim left of decimal
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
Best Answers
-
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
3 -
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"0
Answers
-
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
3 -
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"0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive