I have a column of part numbers and I need to exclude any rows where a part number has a character
Best Answer
-
@TripleZ I agree with Mark's solution. You can also try using the below formula in a filter tile to check whether the value has only numeric characters. The REGEXP_LIKE function will return 1 if it matches and 0 if it does not, so you can specify that you only want to keep the matches
REGEXP_LIKE(`Part`,'[0-9]*') = 1
1
Answers
-
You can use the split columns tile in Magic ETL to split up your string by looking for the comma. You would do multiple splits in that tile so that you can get to the 3rd and 4th entries. You can then do a filter tile where you filter out entries that have the values you want to exclude. Here's a KB article on the split columns tile.
You can also use the SPLIT_PART function to do this in a formula tile.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
I think I was unclear in my explanation. Each of those 4 values are in the same column on different rows. I need a way to look at a column and determine if the first or last character is non-numeric.
Thanks,
Mike
0 -
You can use LEFT(fieldname,1) to get the first character and RIGHT(fieldname,1) to get the last character. You can then test for numeric by using TRY_CAST(leftcharacter as integer). If it returns null then it is non-numeric.
I would create separate fields in your ETL for each of these functions so it is easier to test as you work through it.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@TripleZ I agree with Mark's solution. You can also try using the below formula in a filter tile to check whether the value has only numeric characters. The REGEXP_LIKE function will return 1 if it matches and 0 if it does not, so you can specify that you only want to keep the matches
REGEXP_LIKE(`Part`,'[0-9]*') = 1
1 -
Thanks for the follow up. the REGEXP_LIKE worked for me.
Mike
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive