Alternating colours based on multiple different groups in a table

daniela_zuza — Wed, 16 Nov 2022 14:54:19 GMT

Hello, I am using Qlik Sense actually, but I need to colour the background of cells in a table based on the different grouped values. It's easier to show this on the example below, which I achieved in excel by adding another column which alternates from 0 to 1 when the grouping changes, and then applying the colour based on that.

Is this the same way to proceed in Qlik, or is there a direct way to do it?

I have too many groups to be able to use an IF clause alongside specifying each one.

Re: Alternating colours based on multiple different groups in a table

hoangvvo — Wed, 16 Nov 2022 16:30:05 GMT

if the column's value contain only a few values and you know what they are , pick(match(column,'first','second'...),RGB(),RGB(),RGB())

this will return the color if the values match. first , second etc. apply this to the column's background expression.

if your data grows and the column changes or new values added etc. and you want to just alternate between 2 color for group:

load your table in sorted sequence that you would like to see on the table chart (in this case column is the field name)

then use your table in another load statement only loading the distinct value of your grouping, assign an id using recno() as a preceding load, which you will use to load your distinct table using mod(id,2) as flag

this will result in a distinct group name, and a 0,1 flag, then you join it back to your primary table.

here's the sample :

//this is your primary table, because qlik aggregate the same dimensional values on chart display i added rowid to this table
t:NoConcatenate
load *
,recno() as rowid;
load * inline
[Column1
AAA
AAA
AAA
EEE
EEE
AAA
BBB
BBB
CCC
AAA
AAA
CCC
CCC
DDD
BBB
BBB
DDD
DDD
EEE
];

//create a distinct table with values of the grouping field
idxtable:NoConcatenate
load distinct Column1
resident t;

//load them into another table adding recno() as an id for each grouping field value, sort it in teh preceding load
//then use that for mod(id,2) to return either 0 or 1 as flag
tmp2:NoConcatenate
load * ,
mod(id,2) as flag;
load Column1
,RecNo() as id
resident idxtable
order by Column1;

drop table idxtable;

//drop the table and join the flag field back into your main table.
inner join ('t')
load Column1,
flag
resident tmp2;

drop table tmp2;

The result is a table (t) with Column1, rowid, flag

where flag will be either 1 or 0

if you sort your chart using the same column they should all group as 1 or 0

then in each column just add the if(flag=0,rgb(),rgb()) should alternate the coloring for the group

EDIT: This is for qlik Sense, i dont use qlik view but i am sure the load should work

Re: Alternating colours based on multiple different groups in a table

daniela_zuza — Mon, 21 Nov 2022 14:31:36 GMT

thank you so much for this! I use Qlik Sense too & will give it a go.

I have probably over 20,000 distinct values in that column, so the flag method is something I was looking for, thank you!

topic Re: Alternating colours based on multiple different groups in a table in QlikView

Alternating colours based on multiple different groups in a table

Re: Alternating colours based on multiple different groups in a table

Re: Alternating colours based on multiple different groups in a table