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.
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.
@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.
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' …
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
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'
Is your data in all uppercase? The evaluations can be case-sensitive.
Hey Mark, For the prescribing facility, everything is uppercase
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.
THANK YOU BOTH! I got it to work with the Trim and Like statements.
