Formula to search text
Hi domo fam!
I am currently working on two ELT formulas: the first I am trying to search a string field column named Note for a specific text. However the way the text is stored in the row it is “Bankruptcy Record-then a break line-Dismissal Customer confirmed bankruptcy on 01-01-2021”
I want to search for all notations that has Bankruptcy Record Dismissal... the formula I was using is:
Case (when (str_contains(Note, ‘Bankruptcy Record Dismissal’ = ‘1’) then ‘Dismissal’ End)
The formulated column comes back null because of the line break in the notes. I know in sql you would query:
where Note like “Bankruptcy Record”+CHAR(10)+”Dismissal”
but I cannot input CHAR10 in the formula tile in ELT Mode. Anyway to search for “Bankruptcy Record Dismissal” knowing there is a line break after record?
—————————————-
Secondly trying to mask numbers in the same notes field. I used the remove digit formula however I wanted to replace any digit to be displayed as ####.
Your assistance with this is appreciated!
Comments
-
For the text search, have you tried
CASE WHEN 'Note' LIKE '%Bankruptcy Record Dismissal%' THEN 'Dismissal' ELSE 'Other' END'
For the number masking, you can probably do it with regex. You could also use multiple replace functions to replace each number with a #, such as this:
REPLACE(REPLACE(REPLACE('field','0','#'),'1','#'),'2','#)
Keep following this all the way to 9.
**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 -
Slight correction. Percent is the same as a * wild card. So to accommodate the newline char add %
CASE WHEN 'Note' LIKE '%Bankruptcy Record%Dismissal%' THEN 'Dismissal' ELSE 'Other' END'
Domo Formula tiles do support REGEX expressions so just google "regex remove numeric"
something like
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Thanks! I thought I tried LIKE and it didn't work at the time, but maybe with the recent updates since Domopalooza it works now. I do have one follow up question because I'm getting some text as the below example:
"Bankruptcy Record
Miscellaneous
Customer sent notice of impending dismissal."
When I utilize the below:
MISCELLANEOUS COLUMN CREATED:
CASE
When 'Note' LIKE 'Bankruptcy Record%Miscellaneous'then 'Miscellaneous'
End
DISMISSAL COLUMN CREATED:
Case
When 'Note' LIKE 'Bankruptcy Record%Dismissal' then Dismissal'
End
I would coalesce both columns for a new column for Total "Bankruptcy Outcome" however, since Dismissal and Miscellaneous is found in some text it would sometimes display the Outcome as Dismissal when it should be Miscellaneous. The second line of the text is what we would select from a drop down menu that is labeled as the outcome. How can I search the text to read only the first two lines.
@jaeW_at_Onyx @MarkSnodgrass @GrantSmith Thanks for your help so far 😊
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
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 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
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive