pick function

[Anonymous]

Hi,

I'd like to set a function to select the right colour for a chart.

The simlest way to do it is as follows:

pick(match(brand,'A','B','C'),colour1,colour2,colour3)

But the point is that I have 100 brands and 100 user-defined colours. The colour values are set in a field in a table. Keeping the colours in a table is more convenient than keeping them as separate variables.

So, there is a field [Colour] with 100 values.

Is it possible to use a reference to the field [Colour] instead of the list 'colour1,colour2,colour3,... colour100' in a pick function?

If not, then what other function can I use to select colours that are kept as field values?

Thank you in advance,

Larisa

[giakoum]

I guess you use brand as a dimension, so just create a new table in your model, with fields brand and color like that :

Brand     BrandColor

A     RGB(0.120.100)

B     RGB(0,0,100)

values are for example purposes only

Then use that new field in the background color expression

[MK_QSL]

Read below Blog..

Colors in charts

[tresesco]

Try with concat(), like:

vColor=Concat( Distinct ColorField, ',')

pick(match(brand,'A','B','C'), $(vColor))

You might wantto follow the similar approach for Brand field as well. Only thing you have to be careful about the ordering of the values in the concat(). You might have to use chr(39) as well to get the properly formatted string.

Or,

You might associate Brand and ColorField in the script.

[sudeepkm]

You can use the field value like below.

use $(=color) in the expression

[Peter_Cammaert]

If you use an evaluate() call during color table load, color specifications will be converted from a text value (e.g. 'RGB(R,G,B)' to a real QV color values. For example, if you load a coloring scheme like this:

:

ColorTable:

LOAD Brand, evaluate(BrandColor) AS BrandColor FROM Excel.xlsx ();

:

then you can use this simple expression in your Background Color field:

=BrandColor

Note that text strings like RGB(1,2,3) cannot be used in an INLINE data list, as the INLINE will treat the commas as field separators. You'll have to use a different character like #, and add an additional function to the column expression, like:

... evaluate(Replace(BrandColor,'#',',')) AS BrandColor ...

Best,

Peter

[Anonymous]

I thank everyone for your answers!

The point is that I don't want to associate the brands with the colours. Besided brands, I've got other dimensions: subbrands, advertisers etc. All these dimensions must have user-defined colours. That's why I'd like to keep my user-defined colours in a separate table and be able to use it for any dimension: brands, subbrands, advertisers, etc.

I'll try a piece of advice which implies the idea of using concat().

[#8]

Hi Larisa,

Create a Mapping Table having brand and color and then use applymap().

Regards

Harsha

[#9]

Hi

Please try this expressions

vColor=Concat( Distinct ColorField, ',')

pick(match(brand,'A','B','C'), $(vColor))

[Anonymous]

I set:

vColor = Concat( Distinct ColourField2, ',')

=pick(1, $(vColor))

But it doesn't pick the first color. It picks the whole string.

How can I fix it?

see the file attached.

I evaluated the field ColourField2 in the script as real QlikView colors.