Formula to search text

Shumilex

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!

MarkSnodgrass

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.

jaeW_at_Onyx

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

https://stackoverflow.com/questions/1657282/how-to-remove-numbers-from-string-using-regex-replace/1657361

Shumilex

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 😊