Counting similar values across multiple fields

[Dan_A]

Hi,

I need to count up multiple values across several different fields:

User	Colour A	Colour B	Colour C	Colour D
James	GREEN	BLUE	-	GREEN
Charlotte	BLUE	GREEN	BLUE	-
Neil	YELLOW	YELLOW	YELLOW	BLUE
Lauren	GREEN	BLUE	-	YELLOW
Sarah	YELLOW	YELLOW	YELLOW	-

I need to see how often each value is used, so that the values become the dimensions:

YELLOW	7
GREEN	4
BLUE	5

I also need to be able to filter at User level, so filtering on James would show

YELLOW	-
GREEN	2
BLUE	1

I hope this makes sense, thanks!!

[henrikalmen]

Flatten the table in backend. Something like:

new_table:
load User, [Colour A] as Colour resident old_table where len([Colour A])>0;
concatenate load User, [Colour B] as Colour resident old_table where len([Colour B])>0;
concatenate load User, [Colour C] as Colour resident old_table where len([Colour C])>0;
concatenate load User, [Colour D] as Colour resident old_table where len([Colour D])>0;

From this new table you can count colours per User.

[Kushal_Chawda]

@Dan_A  Checkout cross table load

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

[henrikalmen]

Yes, crosstable is even better (slightly more advanced but especially if
there are many columns it's a better option).