topic count distinct concat results in QlikView https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621176#M445800 <P>This is my problem:</P><P>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.</P><P>&nbsp;</P><P>Data table:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>role</TD><TD>rolelevel</TD><TD>skill</TD></TR><TR><TD>Admin</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>schreiben</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>schreiben</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>1</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>1</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>2</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>2</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>3</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>3</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>4</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>reden</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>denken</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>reden</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>denken</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>reden</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Table in Qlik using concate() to build a skill profile</P><TABLE><TBODY><TR><TD>role</TD><TD>rolelevel</TD><TD>skill profile= concat(skill,', ')</TD></TR><TR><TD>Admin</TD><TD>1</TD><TD>lesen,&nbsp;</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>lesen, rechnen,&nbsp;</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>hören, lesen, reden</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>denken, lesen, reden</TD></TR><TR><TD>User</TD><TD>1</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>User</TD><TD>2</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>User</TD><TD>3</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>User</TD><TD>4</TD><TD>lesen, rechnen, reden, schreiben</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>And this is the table I want to generate dynamically:</P><TABLE><TBODY><TR><TD>role</TD><TD>distinct profiles</TD></TR><TR><TD><P>Admin</P></TD><TD>3</TD></TR><TR><TD><P>Contributer</P></TD><TD>2</TD></TR><TR><TD>User</TD><TD>2</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>When I say "dynamically", I mean that when filtering to only a subset of skills (e.g. hören, lesen, denken) I get:</P><TABLE><TBODY><TR><TD>role</TD><TD>rolelevel</TD><TD>concat(skill,', ')</TD></TR><TR><TD>Admin</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>hören, lesen</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>denken, lesen</TD></TR><TR><TD>User</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>lesen</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>and again what I try to build is</P><TABLE><TBODY><TR><TD>role</TD><TD>distinct profiles</TD></TR><TR><TD><P>Admin</P></TD><TD><P>1</P></TD></TR><TR><TD><P>Contributer</P></TD><TD>2</TD></TR><TR><TD>User</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I tried something like</P><P>count(distinct concat(skill , ', ')</P><P>or even I tried to count the distinct strings virtually created by AGGR in</P><P>count(distinct aggr( concat(skill,', '), rolelevel))</P><P>but none showed the expected results.</P><P>&nbsp;</P><P>Any ideas?</P> Sat, 16 Nov 2024 02:32:44 GMT michael_klix 2024-11-16T02:32:44Z count distinct concat results https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621176#M445800 <P>This is my problem:</P><P>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.</P><P>&nbsp;</P><P>Data table:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>role</TD><TD>rolelevel</TD><TD>skill</TD></TR><TR><TD>Admin</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>schreiben</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>schreiben</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>1</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>1</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>2</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>2</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>3</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>3</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>schreiben</TD></TR><TR><TD>User</TD><TD>4</TD><TD>rechnen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>reden</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>denken</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>reden</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>denken</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>reden</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Table in Qlik using concate() to build a skill profile</P><TABLE><TBODY><TR><TD>role</TD><TD>rolelevel</TD><TD>skill profile= concat(skill,', ')</TD></TR><TR><TD>Admin</TD><TD>1</TD><TD>lesen,&nbsp;</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>lesen, rechnen,&nbsp;</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>hören, lesen, reden</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>denken, lesen, reden</TD></TR><TR><TD>User</TD><TD>1</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>User</TD><TD>2</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>User</TD><TD>3</TD><TD>lesen, rechnen, schreiben</TD></TR><TR><TD>User</TD><TD>4</TD><TD>lesen, rechnen, reden, schreiben</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>And this is the table I want to generate dynamically:</P><TABLE><TBODY><TR><TD>role</TD><TD>distinct profiles</TD></TR><TR><TD><P>Admin</P></TD><TD>3</TD></TR><TR><TD><P>Contributer</P></TD><TD>2</TD></TR><TR><TD>User</TD><TD>2</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>When I say "dynamically", I mean that when filtering to only a subset of skills (e.g. hören, lesen, denken) I get:</P><TABLE><TBODY><TR><TD>role</TD><TD>rolelevel</TD><TD>concat(skill,', ')</TD></TR><TR><TD>Admin</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>Admin</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>Contributer</TD><TD>1</TD><TD>hören, lesen</TD></TR><TR><TD>Contributer</TD><TD>2</TD><TD>denken, lesen</TD></TR><TR><TD>User</TD><TD>1</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>2</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>3</TD><TD>lesen</TD></TR><TR><TD>User</TD><TD>4</TD><TD>lesen</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>and again what I try to build is</P><TABLE><TBODY><TR><TD>role</TD><TD>distinct profiles</TD></TR><TR><TD><P>Admin</P></TD><TD><P>1</P></TD></TR><TR><TD><P>Contributer</P></TD><TD>2</TD></TR><TR><TD>User</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I tried something like</P><P>count(distinct concat(skill , ', ')</P><P>or even I tried to count the distinct strings virtually created by AGGR in</P><P>count(distinct aggr( concat(skill,', '), rolelevel))</P><P>but none showed the expected results.</P><P>&nbsp;</P><P>Any ideas?</P> Sat, 16 Nov 2024 02:32:44 GMT https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621176#M445800 michael_klix 2024-11-16T02:32:44Z Re: count distinct concat results https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621376#M445810 <P>Hi Michael, maybe with:&nbsp;Max(aggr(Count(DISTINCT skill), role, rolelevel))</P> Sun, 08 Sep 2019 21:29:41 GMT https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621376#M445810 rubenmarin 2019-09-08T21:29:41Z Re: count distinct concat results https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621556#M445819 <P>Thanks for pointing into the right direction <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P>&nbsp;</P><P>Actually the final solution was</P><P><FONT color="#0000FF">Count</FONT> (&nbsp; <FONT color="#0000FF">distinct &nbsp;aggr</FONT>( <FONT color="#0000FF">concat</FONT>(<FONT color="#FF0000">skill</FONT>)&nbsp; , <FONT color="#FF0000">role</FONT>, <FONT color="#FF0000">rolelevel</FONT>)&nbsp;&nbsp;&nbsp; )</P><P>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.</P> Mon, 09 Sep 2019 10:39:21 GMT https://community.qlik.com/t5/QlikView/count-distinct-concat-results/m-p/1621556#M445819 michael_klix 2019-09-09T10:39:21Z