count distinct concat results

[michael_klix]

This is my problem:

I have a table with three dimensions, comparable to the following dummy data and I want to generate dynamic skill profiles and count, how many distinct skill profiles a role has.

Data table:

role	rolelevel	skill
Admin	1	lesen
Admin	2	lesen
Admin	2	schreiben
Admin	3	lesen
Admin	3	schreiben
Admin	3	rechnen
User	1	lesen
User	1	schreiben
User	1	rechnen
User	2	lesen
User	2	schreiben
User	2	rechnen
User	3	lesen
User	3	schreiben
User	3	rechnen
User	4	lesen
User	4	schreiben
User	4	rechnen
User	4	reden
Contributer	1	denken
Contributer	1	reden
Contributer	1	lesen
Contributer	2	denken
Contributer	2	lesen
Contributer	2	reden

Table in Qlik using concate() to build a skill profile

role	rolelevel	skill profile= concat(skill,', ')
Admin	1	lesen,
Admin	2	lesen, rechnen,
Admin	3	lesen, rechnen, schreiben
Contributer	1	hören, lesen, reden
Contributer	2	denken, lesen, reden
User	1	lesen, rechnen, schreiben
User	2	lesen, rechnen, schreiben
User	3	lesen, rechnen, schreiben
User	4	lesen, rechnen, reden, schreiben

And this is the table I want to generate dynamically:

role	distinct profiles
Admin	3
Contributer	2
User	2

When I say "dynamically", I mean that when filtering to only a subset of skills (e.g. hören, lesen, denken) I get:

role	rolelevel	concat(skill,', ')
Admin	1	lesen
Admin	2	lesen
Admin	3	lesen
Contributer	1	hören, lesen
Contributer	2	denken, lesen
User	1	lesen
User	2	lesen
User	3	lesen
User	4	lesen

and again what I try to build is

role	distinct profiles
Admin	1
Contributer	2
User	1

I tried something like

count(distinct concat(skill , ', ')

or even I tried to count the distinct strings virtually created by AGGR in

count(distinct aggr( concat(skill,', '), rolelevel))

but none showed the expected results.

Any ideas?

[rubenmarin]

Hi Michael, maybe with: Max(aggr(Count(DISTINCT skill), role, rolelevel))

[michael_klix · Accepted solution]

Thanks for pointing into the right direction 🙂

Actually the final solution was

Count (  distinct  aggr( concat(skill)  , role, rolelevel)    )

The logic: First using AGGR build a table over all roles and levels with the individual concatenated skills as string and THEN put this into a table with just the role as dimension. Then it will count per role how many distinct strings (=skill profiles) per role do exist.