Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Instead of doing this why don't you create a mapping table in the script for Q1, Q2 etc with corresponding month
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')
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.
Match (Frequency('Q1''17', 'Q2''17', 'Q3''17', 'Q4''17', 'Apr', 'May', 'Jun'. 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar')
regards
Pradosh
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.