Apostrophe messing up formula

Josh_Godec123

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.

MarkSnodgrass

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.

MichelleH

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

GrantSmith

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' …

MarkSnodgrass

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

Josh_Godec123

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'

MarkSnodgrass

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

Josh_Godec123

Hey Mark, For the prescribing facility, everything is uppercase

MarkSnodgrass

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.

MarkSnodgrass

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.

MichelleH

@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

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