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!
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:
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"
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:
Customer sent notice of impending dismissal."
When I utilize the below:
MISCELLANEOUS COLUMN CREATED:
When 'Note' LIKE 'Bankruptcy Record%Miscellaneous'then 'Miscellaneous'
DISMISSAL COLUMN CREATED:
When 'Note' LIKE 'Bankruptcy Record%Dismissal' then Dismissal'
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
- 7.7K All Categories
- 5 Connect
- 921 Connectors
- 244 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 40 Visualize
- 199 Beast Mode
- 2K Charting
- 8 Variables
- 20 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 28 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部