match function for the Fields with single quote

[narayanamsn]

Hi,

I am trying to use forced sort using match function to re-arrange the sequence in the graph.

Field Name: Frequency

Values: Q1'17, Q2'17, Q3'17, Q4'17, Apr, May, Jun. Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar.

Match (Frequency('Q1'17', 'Q2'17', 'Q3'17', 'Q4'17', 'Apr', 'May', 'Jun'. 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar')

As the values of Field does have ' (single quote), hence it is throwing error.

How to take care of this?

[avinashelite]

Instead of doing this why don't you create a mapping table in the script for Q1, Q2 etc with corresponding month

[devarasu07]

Hi,

chr(39) function used to handle the single code.

try like this

Match(Frequency,'Q1'&chr(39)&'17', 'Q2'&chr(39)&'17', 'Q3'&chr(39)&'17', 'Q4'&chr(39)&'17', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar')

[tresesco]

Put an additional single quote that would work as an escape sequence. Try like:

Match (Frequency, 'Q1''17', 'Q2''17', .....

Note: Two single quotes and not double quote.

[pradosh_thakur]

Match (Frequency('Q1''17', 'Q2''17', 'Q3''17', 'Q4''17', 'Apr', 'May', 'Jun'. 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar')

regards

Pradosh

[jonathandienst]

Just double up the single quote marks to include them in the string (ie to escape them):

Match (Frequency, 'Q1''17', 'Q2''17', 'Q3''17', 'Q4''17', 'Apr', 'May', 'Jun'. 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar')

Note that is two single quotes, not a double quote.