Skip to content

    • Beast Mode

    Beast Mode

    Apostrophe messing up formula

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      Answer ✓

      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. 

      1. CASE
      2. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BISCAYANE%' THEN 'MIAMi-DADE COUNTY'
      3. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%SOUTH BEACH%' THEN 'MIAMi-DADE COUNTY'
      4. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%JACKSONVILLE%' THEN 'CENTRAL FLORIDA'
      5. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BROWARD%' THEN 'BROWARD COUNTY'
      6. …
      7. 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.
    • Coach
      Answer ✓

      @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

    • Coach

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

      1. 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!**
    • Coach

      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.
    • Member

      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
    • Coach

      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.
    • Member

      Hey Mark, For the prescribing facility, everything is uppercase

      image.png
    • Coach

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

      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. 

      1. CASE
      2. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BISCAYANE%' THEN 'MIAMi-DADE COUNTY'
      3. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%SOUTH BEACH%' THEN 'MIAMi-DADE COUNTY'
      4. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%JACKSONVILLE%' THEN 'CENTRAL FLORIDA'
      5. WHEN TRIM(UPPER(prescribingfacility)) LIKE '%BROWARD%' THEN 'BROWARD COUNTY'
      6. …
      7. 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.
    • Coach
      Answer ✓

      @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.

    • Member

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories