Color by Expression

[#1]

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!!

[sergio0592]

Hi, you can try this:

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

[#3]

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

[MarcoWedel]

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

[MarcoWedel]

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

[sergio0592]

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?

[MarcoWedel]

- see example below

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

regards

Marco

[johnw]

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.

[Anonymous]

Hi Pauline ,

   Try with following Expression

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

Regards

Vinod

[MarcoWedel]

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