Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Hi Michael, maybe with: Max(aggr(Count(DISTINCT skill), role, rolelevel))
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.