Formula to search text

Shumilex
Shumilex Member
edited April 2021 in Magic ETL

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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

    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"
  • Shumilex
    Shumilex Member
    edited April 2021

    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 😊

Categories