topic Re: pick function in QlikView

pick function

Anonymous — Wed, 25 Mar 2015 07:44:57 GMT

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

Re: pick function

giakoum — Wed, 25 Mar 2015 07:50:59 GMT

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

Re: pick function

MK_QSL — Wed, 25 Mar 2015 07:58:36 GMT

Read below Blog..

Colors in charts

Re: pick function

tresB — Wed, 25 Mar 2015 07:59:45 GMT

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.

Re: pick function

sudeepkm — Wed, 25 Mar 2015 08:09:56 GMT

You can use the field value like below.

use $(=color) in the expression

Re: pick function

Peter_Cammaert — Wed, 25 Mar 2015 08:10:54 GMT

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

Re: pick function

Anonymous — Wed, 25 Mar 2015 09:13:49 GMT

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().

Re: pick function

Wed, 25 Mar 2015 09:25:14 GMT

Hi Larisa,

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

Regards

Harsha

Re: pick function

Wed, 25 Mar 2015 11:12:11 GMT

Please try this expressions

vColor=Concat( Distinct ColorField, ',')

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

Re: pick function

Anonymous — Wed, 25 Mar 2015 12:34:05 GMT

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.

Re: pick function

Anonymous — Wed, 25 Mar 2015 13:35:26 GMT

Hi, Tresesco, Narven Raja,

The Concat function works only if I set it like this:

vColor=Concat( Distinct ColorField, ',')

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

Thanks for your help!

Larisa