topic Re: match function for the Fields with single quote in QlikView

match function for the Fields with single quote

narayanamsn — Fri, 03 Nov 2017 06:43:36 GMT

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?

Re: match function for the Fields with single quote

avinashelite — Fri, 03 Nov 2017 06:47:03 GMT

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

Re: match function for the Fields with single quote

devarasu07 — Fri, 03 Nov 2017 07:00:22 GMT

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

Re: match function for the Fields with single quote

tresB — Fri, 03 Nov 2017 07:02:28 GMT

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.

Re: match function for the Fields with single quote

pradosh_thakur — Fri, 03 Nov 2017 07:10:57 GMT

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

regards

Pradosh

Re: match function for the Fields with single quote

jonathandienst — Fri, 03 Nov 2017 07:20:51 GMT

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.