Variable using match function doesn't work

[GeorgeKokkinopoulos]

Hello,

I am using the following script in Data Load Editor: 

Define field 'Colour' with 4 values

LOAD

* INLINE
[Colour 

Red

Blue

Green

Yellow

];

Then define variables below:

SET vMyColour='match(My_Colour,[Colour])'

SET vYourColour='match(Your_Colour,[Colour])'  (My_Colour and Your_Colour are two fields from the dataset I load).

SET vDiffCol= '$(vMyColour) and not $(vYourColour)';

Then I create a filter (in Filter Pane) for Colour and use vDiffCol in my measures. It seems it works fine when I filter for one colour ONLY
 but when I select two colours (e.g Red and Blue) then match function doesn't seem to do match(My_Colour,'Red','Blue') as expected.
For example if My_Colour='Red' and Your_Colour='Blue' I would expect both vMyColour and vYourColour to be 'True' therefore
vDiffCol should be 'False' but for some reason it still comes 'True'. Any help.
Thanks
George  

[Or · Accepted solution]

When you place a field (Colour) into a function such as Match, if that field has more than one value possible, the result will not be a concatenation of the possible values. You'd have to tell Qlik you wanted to concatenate the results if that's what you intend to do, using concat, getfieldselections, or some other function as relevant.

[GeorgeKokkinopoulos]

Thanks Or.  The solution which worked for me was to create the following dimension:

match(My_Colour,$(=concat(chr(39)&[Colour]&chr(39), ',')))

However, one thing I couldn't do was to create it as a variable in Load Editor which was my original purpose. The reason is that I coudn't find a way to escape the single quotes in ',' near the end of the statement. Does anyone know a way I can do it? The report has been created fine but it would be helpful for me to create simpler calculations by creating a variable in Load Editor first in future reports.

Thanks again

George  

[Or]

Generally, the escape sequence for a single quote is two single quotes. See:

https://community.qlik.com/t5/Qlik-Design-Blog/Escape-sequences/ba-p/1469770