topic Re: Color by Expression in QlikView

Color by Expression

Wed, 25 Nov 2020 16:16:04 GMT

Does anyone know the syntax to use the "color by expression" function for multiple variables?

It's pretty simple for If the owner equals "Mr. Smith" then make him purple, if not make everything else gray.....

This syntax works for the the above.

if([Owner]='Mr. Smith','purple','drkgray')

The issue is that I have 3 variables that I am trying to color code. I am trying to do something specific:

Mr. Smith= purple

Mr. Jones=drkgray

Mr. Wallace= green.

Please help!

Thanks!!

Re: Color by Expression

sergio0592 — Mon, 07 Nov 2016 21:34:41 GMT

Hi, you can try this:

=pick(match(Owner,'Mr Smith','Mr Jones','Mr Wallace'),purple(),darkgray(),green())

Re: Color by Expression

Mon, 07 Nov 2016 21:48:00 GMT

if([Name]='Smith',rgb(128,0,128),if([Name]='Jones', darkgray(), green() ))

Re: Color by Expression

MarcoWedel — Mon, 07 Nov 2016 23:19:11 GMT

Better load a table with Owner and Colour fields instead of using static values in expressions.

Re: Color by Expression

MarcoWedel — Mon, 07 Nov 2016 23:41:19 GMT

if you can do with QlikView's predefined Color functions‌ then one solution could be:

tabOwnerColours:
LOAD Owner,
    Dual(Colour, Evaluate(Colour&'()')) as Colour
Inline [
Owner, Colour
Mr. Smith,  Magenta
Mr. Jones, DarkGray
Mr. Wallace, Green
];

hope this helps

regards

Marco

Re: Color by Expression

sergio0592 — Mon, 07 Nov 2016 23:42:26 GMT

Marco i have two questions :

-how to use a loaded color table as you proposed?

-why table is better than formula, i suppose less greedy for CPU?

Re: Color by Expression

MarcoWedel — Mon, 07 Nov 2016 23:47:56 GMT

- see example below

- I'm rather concerned about maintainability than performance in this case.

regards

Marco

Re: Color by Expression

johnw — Tue, 08 Nov 2016 00:24:34 GMT

Or if you want colors other than the named ones and live in America where we dropped the U in a lot of words for unknown reasons:

OwnerColors:
LOAD *
,rgb(R,G,B) as Color
INLINE [
Owner,R,G,B
Mr. Smith,200,100,200
Mr. Jones,100,100,100
Mr. Wallace,100,200,100
];

And I do this sort of thing in several applications. I like it best when I'm trying to associate the color consistently throughout the application rather than in one place. I do, though, use a variable vColor instead of Color. And it might, for instance, have a simple expression like this:

alt(max(Color),rgb(180,180,180))

To play devil's advocate, if you're going to define a variable anyway, as I do, what's the point of creating the table? We could just put it all in our variable. But still, somehow the table just seems more clear to me. On the down side, if you want to make color changes, you have to run a reload, and that could be a headache for long-running scripts. Then I really might argue for leaving it in a variable so that it's more maintainable in practice.

Re: Color by Expression

Anonymous — Tue, 08 Nov 2016 06:15:15 GMT

Hi Pauline ,

Try with following Expression

pick(match(Name(Owner),'Mr Smith','Mr Jones','Mr Wallace'),purple(),darkgray(),green())

Regards

Vinod

Re: Color by Expression

MarcoWedel — Tue, 08 Nov 2016 22:57:37 GMT

Hi,

if the QlikView inbuilt color functions aren't enough, then maybe something like this could help as well:

FOR Each vSubTable in 'A%E2%80%93F', 'G%E2%80%93M', 'N%E2%80%93Z'
tabColour:
LOAD Dual(Name,Num#('FF'&Mid([Hex (RGB)],2),'(HEX)')) as Colour 
 FROM [https://en.wikipedia.org/wiki/List_of_colors:_$(vSubTable)] (html, codepage is 1252, embedded labels, table is [Color names]);
NEXT vSubTable

mapColour:
Mapping LOAD Text(Colour), Colour Resident tabColour;

tabOwnerColours:
LOAD Owner,
    ApplyMap('mapColour', Colour) as Colour
Inline [
Owner, Colour
Mr. Smith,  Lavender purple
Mr. Jones, Granite Gray
Mr. Wallace, Lime green
];

(only issue in this case seems to be that there are colour names having common codes, resulting in the first occurrence defining the text representation of the dual colour value ...)

regards

Marco

Re: Color by Expression

sergio0592 — Wed, 16 Nov 2016 18:37:31 GMT

Marco, i'd like understand why you use : Dual(Colour, Evaluate(Colour&'()')) as Colour in your script.

I'am sure there is a very good reason because if i remove it from load statement and write Magenta(), DarkGray(), Green() in Colour, it won't works

Thanks for your answer.

Re: Color by Expression

MarcoWedel — Wed, 16 Nov 2016 21:29:49 GMT

Hi,

this expression generates a dual value (meaning having both text and numeric values).

The text part is the Colour string itself, while the numeric part is the result of the QlikView inbuilt colour function with the same name:

The string "Green" is extended to "Green()" and the result evaluated as a QlikView expression (see also: Color functions) in this case delivering the RGB Value "RGB(0,128,0)".

Hope this helps

regards

Marco

Re: Color by Expression

sergio0592 — Wed, 16 Nov 2016 22:06:08 GMT

Thanks, it's very clear now!