Apostrophe messing up formula

Options
Josh_Godec123
Josh_Godec123 Member
in Beast Mode

I have a datarow with an apostrophe, but the apostrophe it messes up my beast mode formula. Any ideas on a workaround for this, I do not have access to change the incoming data.

image.png

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓
    Options

    You can also use a LIKE statement and use wildcard matching depending on what your data looks like. As an example, here is what the first few rows would look like as a CASE statement. 

    CASE
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BISCAYANE%' THEN 'MIAMi-DADE COUNTY'
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%SOUTH BEACH%' THEN 'MIAMi-DADE COUNTY'
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%JACKSONVILLE%' THEN 'CENTRAL FLORIDA'
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BROWARD%' THEN 'BROWARD COUNTY'
…
    
END

    Using the % sign on both sides will allow it to search for that phrase in the string. You can remove the % sign at the beginning if you want to ensure that it only starts with that phrase. This would eliminate your apostrophe issue.

    Just another option to consider.

    **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.
  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @Josh_Godec123 It looks like "CARL BEAN MEN'S WELLNESS CENTER" is not specified in your case statement, so it would fall into your ELSE 0 bucket. I second @MarkSnodgrass's suggestion of using the LIKE operator to capture patterns rather than exact values.

Answers

  • GrantSmith
    GrantSmith Coach
    Options

    You can use two single quotes together to escape it and have it treated as a single quote in your string

    when `Prescribing Facility` = 'BISCAYNE MEN''S WELLNESS CENTER' …

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Options

    If you put two single quotes there instead of just one, it will know you will want to use a single quote.

    BISCAYANE MEN''S WELLNESS CENTER

    **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.
  • Josh_Godec123
    Josh_Godec123 Member
    Options

    when i add the double quotes it resolves the error message with the formula, but it is not mapping correctly on the backend to the county I am trying to map it to.. Its comes up as my else '0'

    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach
    Options

    Is your data in all uppercase? The evaluations can be case-sensitive.

    **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.
  • Josh_Godec123
    Josh_Godec123 Member
    Options

    Hey Mark, For the prescribing facility, everything is uppercase

    image.png

  • MarkSnodgrass
    MarkSnodgrass Coach
    Options

    You might try wrapping a TRIM() function around your prescribing facility field in your case statement to rule out any hidden spaces that are potentially causing it not to match.

    **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.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓
    Options

    You can also use a LIKE statement and use wildcard matching depending on what your data looks like. As an example, here is what the first few rows would look like as a CASE statement. 

    CASE
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BISCAYANE%' THEN 'MIAMi-DADE COUNTY'
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%SOUTH BEACH%' THEN 'MIAMi-DADE COUNTY'
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%JACKSONVILLE%' THEN 'CENTRAL FLORIDA'
    
WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BROWARD%' THEN 'BROWARD COUNTY'
…
    
END

    Using the % sign on both sides will allow it to search for that phrase in the string. You can remove the % sign at the beginning if you want to ensure that it only starts with that phrase. This would eliminate your apostrophe issue.

    Just another option to consider.

    **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.
  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @Josh_Godec123 It looks like "CARL BEAN MEN'S WELLNESS CENTER" is not specified in your case statement, so it would fall into your ELSE 0 bucket. I second @MarkSnodgrass's suggestion of using the LIKE operator to capture patterns rather than exact values.

  • Josh_Godec123
    Josh_Godec123 Member
    Options

    THANK YOU BOTH! I got it to work with the Trim and Like statements.

Categories