Missing Right Parenthesis Error with Calculated Field using a Variable with Apostraphe

[bdickinson3]

Been receiving an error saying 'Missing Right Parenthesis when attempting to add a calculated field in the Data Manager. I've been trying to create an aggregated nested IF function to put county names based on the ID number. However, I keep getting a "Missing Right Apostrophe Error"  even though I know I have the correct number of parentheses in the code.

Sample of the code that throws the error:

if([COUNTY_NO]= 1, 'Allegany',
if([COUNTY_NO]= 2, 'Anne Arundel',
if([COUNTY_NO]= 3, 'Baltimore',
if([COUNTY_NO]= 4, 'Calvert',
if([COUNTY_NO]= 5, 'Caroline',
if([COUNTY_NO]= 6, 'Carroll',
if([COUNTY_NO]= 7, 'Cecil',
if([COUNTY_NO]= 8, 'Charles',
if([COUNTY_NO]= 9, 'Dorchester',
if([COUNTY_NO]= 10, 'Frederick',
if([COUNTY_NO]= 11, 'Garrett',
if([COUNTY_NO]= 12, 'Harford',
if([COUNTY_NO]= 13, 'Howard',
if([COUNTY_NO]= 14, 'Kent',
if([COUNTY_NO]= 15, 'Montgomery',
if([COUNTY_NO]= 16, 'Prince George''s', 'N/A'))))))))))))))))

...

However, if I omit the last line and remove one parenthesis, it works perfectly

if([COUNTY_NO]= 1, 'Allegany',
if([COUNTY_NO]= 2, 'Anne Arundel',
if([COUNTY_NO]= 3, 'Baltimore',
if([COUNTY_NO]= 4, 'Calvert',
if([COUNTY_NO]= 5, 'Caroline',
if([COUNTY_NO]= 6, 'Carroll',
if([COUNTY_NO]= 7, 'Cecil',
if([COUNTY_NO]= 8, 'Charles',
if([COUNTY_NO]= 9, 'Dorchester',
if([COUNTY_NO]= 10, 'Frederick',
if([COUNTY_NO]= 11, 'Garrett',
if([COUNTY_NO]= 12, 'Harford',
if([COUNTY_NO]= 13, 'Howard',
if([COUNTY_NO]= 14, 'Kent',
if([COUNTY_NO]= 15, 'Montgomery', 'N/A')))))))))))))))

The apostrophe seems to be behind the error, but I can't use quotation marks for the county names and I need the proper county names for an area layer of a map visualization.

[Oleg_Troyansky · Accepted solution]

Hi there!

Clearly, the last county name that includes the single quote in it, causes the issue. The two single quotes are supposed to serve as an "escape-symbol", but they don't seem to work in this particular context - it's possible that it's a bug that needs to be reported. Here are a few work around ideas to consider:

1. Ideally, I'd like to replace this nested IF statement with a mapping table, or even a separate table that could be entered as an INLINE load, or loaded from a text file. It may or may not solve this particular issue, but it will certainly be a "cleaner" solution.

2. You can try replacing the singe quote that gives you trouble with a grave mark ` (usually located to the left of number 1 on most keyboards). It looks similar to a single quote, but it may not break your formula.

3. You can concatenate the single quote, using the function CHR(39), like this:

if([COUNTY_NO]= 1, 'Allegany',
if([COUNTY_NO]= 2, 'Anne Arundel',
if([COUNTY_NO]= 3, 'Baltimore',
if([COUNTY_NO]= 4, 'Calvert',
if([COUNTY_NO]= 5, 'Caroline',
if([COUNTY_NO]= 6, 'Carroll',
if([COUNTY_NO]= 7, 'Cecil',
if([COUNTY_NO]= 8, 'Charles',
if([COUNTY_NO]= 9, 'Dorchester',
if([COUNTY_NO]= 10, 'Frederick',
if([COUNTY_NO]= 11, 'Garrett',
if([COUNTY_NO]= 12, 'Harford',
if([COUNTY_NO]= 13, 'Howard',
if([COUNTY_NO]= 14, 'Kent',
if([COUNTY_NO]= 15, 'Montgomery',
if([COUNTY_NO]= 16, 'Prince George' & CHR(39) & 's', 'N/A'))))))))))))))))

 To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,