How to omit values from a field if it contains a letter inside it
I have a really quick simple question I am using the field "item Number" but I need to omit any Item Numbers that contain the characters "A, B, C, P" I tried using the filter tool on the analyzer and used the Match Values option set that to does not contain "A, B, C, P" but that doesn't seem to work
Here is the screenshot of it:
Another method I have tried is putting this formula in my Dataflow "ItemNumber
BETWEEN '10000' AND '99999' " so it gives me only numbers without any leading letters but this does not seem to produce the correct values either
Is there a case statement I can write in domo beast mode analyzer that would give a true and false based on if there are item numbers that have "A, B, C, P" inside them? Beast mode doesn't seem to have a contain or include a function for me to use.
Best Answers
-
If those are the only letters that will appear in the values of that field you could create a beastmode:
CASE WHEN `ItemNumber` LIKE '%A%' OR `ItemNumber` LIKE '%B%' OR `ItemNumber` LIKE '%C%' OR `ItemNumber` LIKE '%P%' THEN 'Out' ELSE 'In' END
If I solved your problem, please select "yes" above
1 -
CASE WHEN `ItemName` LIKE '%A%' OR `ItemName` LIKE '%B%' OR `ItemName` LIKE '%C%' OR `ItemName` LIKE '%P%' THEN 'Ignore' ELSE 'Keep' END
You can use the LIKE statement to see if your string contains other characters. Alternatively you can use a REGEXP_REPLACE in Magic ETL formula tile:
CASE WHEN REGEXP_LIKE(`ItemName`, '[ABCP]') THEN 'Keep' ELSE 'Ignore' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
This solution worked on one of my datasets, might be worth a shot if there is a wide set of letters that could be in your field. Otherwise, @ColemenWilson and @GrantSmith's suggestions will do the job.
I created this beast mode:
case when length(`field`) = length(`field` * 1) then `keep' else 'ignore' end
Multiplying the field by 1 seems to truncate to the numerical value prior to the letter in the string, so if the lengths do not match after the multiplication, then a non-numeric character must have been present.
If someone knows this method is flawed, let me know!
1
Answers
-
If those are the only letters that will appear in the values of that field you could create a beastmode:
CASE WHEN `ItemNumber` LIKE '%A%' OR `ItemNumber` LIKE '%B%' OR `ItemNumber` LIKE '%C%' OR `ItemNumber` LIKE '%P%' THEN 'Out' ELSE 'In' END
If I solved your problem, please select "yes" above
1 -
CASE WHEN `ItemName` LIKE '%A%' OR `ItemName` LIKE '%B%' OR `ItemName` LIKE '%C%' OR `ItemName` LIKE '%P%' THEN 'Ignore' ELSE 'Keep' END
You can use the LIKE statement to see if your string contains other characters. Alternatively you can use a REGEXP_REPLACE in Magic ETL formula tile:
CASE WHEN REGEXP_LIKE(`ItemName`, '[ABCP]') THEN 'Keep' ELSE 'Ignore' END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you so much for the quick response @ColemenWilson and @GrantSmith appreciate it!✊
0 -
This solution worked on one of my datasets, might be worth a shot if there is a wide set of letters that could be in your field. Otherwise, @ColemenWilson and @GrantSmith's suggestions will do the job.
I created this beast mode:
case when length(`field`) = length(`field` * 1) then `keep' else 'ignore' end
Multiplying the field by 1 seems to truncate to the numerical value prior to the letter in the string, so if the lengths do not match after the multiplication, then a non-numeric character must have been present.
If someone knows this method is flawed, let me know!
1 -
@Sean_Tully - I've used that method as well to convert a string to an integer. I've found it works well.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive