Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
michael_klix
Contributor

count distinct concat results

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:

rolerolelevelskill
Admin1lesen
Admin2lesen
Admin2schreiben
Admin3lesen
Admin3schreiben
Admin3rechnen
User1lesen
User1schreiben
User1rechnen
User2lesen
User2schreiben
User2rechnen
User3lesen
User3schreiben
User3rechnen
User4lesen
User4schreiben
User4rechnen
User4reden
Contributer1denken
Contributer1reden
Contributer1lesen
Contributer2denken
Contributer2lesen
Contributer2reden

 

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

rolerolelevelskill profile= concat(skill,', ')
Admin1lesen, 
Admin2lesen, rechnen, 
Admin3lesen, rechnen, schreiben
Contributer1hören, lesen, reden
Contributer2denken, lesen, reden
User1lesen, rechnen, schreiben
User2lesen, rechnen, schreiben
User3lesen, rechnen, schreiben
User4lesen, rechnen, reden, schreiben

 

And this is the table I want to generate dynamically:

roledistinct profiles

Admin

3

Contributer

2
User2

 

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

rolerolelevelconcat(skill,', ')
Admin1lesen
Admin2lesen
Admin3lesen
Contributer1hören, lesen
Contributer2denken, lesen
User1lesen
User2lesen
User3lesen
User4lesen

 

and again what I try to build is

roledistinct profiles

Admin

1

Contributer

2
User1

 

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?

Labels (3)
1 Solution

Accepted Solutions
michael_klix
Contributor

Re: count distinct concat results

Thanks for pointing into the right direction 🙂

 

Actually the final solution was

Countdistinct  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.

View solution in original post

2 Replies

Re: count distinct concat results

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

michael_klix
Contributor

Re: count distinct concat results

Thanks for pointing into the right direction 🙂

 

Actually the final solution was

Countdistinct  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.

View solution in original post