Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

maelafifi
New Contributor III

Count occurrences

Hi All,

I am trying to count the number of instances for each field.

DummyData.PNG

Please note: This is the script that I am using, and it is a master dimension. I can change the logic within the master dimension, but I cannot change the measures that I am using in the actual app. (I know that this is easily possible if I simply have a separate measure for each of the IDs)

=if(WildMatch(IDs,'1,*')=1 or IDs = '1', 'FATIGUE',

if(WildMatch(IDs,'*,2,*')=1 OR WildMatch(IDs,'*,2*')=1 OR IDs ='2', 'DISTRACTION',

    if(WildMatch(IDs,'*3,*')=1 or IDs = '3' or WildMatch(IDs,'*,3,*')=1, 'COMPLACENCY',

        if(WildMatch(IDs,'*4*')=1 , 'POOR TEAMWORK'

          ))))

1 Solution

Accepted Solutions
MVP
MVP

Re: Count occurrences

Try this

Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 1), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 2), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 3), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 4), IDs)))


Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 1), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 2), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 3), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 4), IDs)))


Capture.PNG

5 Replies
maelafifi
New Contributor III

Re: Count occurrences

stalwar1‌, sorry I wasn't able to attach a document and image in our message, so this was the option I had to proceed with. Any help is greatly appreciated.

Mohamed

MVP
MVP

Re: Count occurrences

Try this

Dimension

=ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK')

Expression

Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

Sum(Aggr(NODISTINCT SubStringCount(IDs, 1), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(IDs, 2), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(IDs, 3), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(IDs, 4), IDs)))


Capture.PNG

maelafifi
New Contributor III

Re: Count occurrences

Hmm, I guess I should have been more thorough with my example. Please see the attached qvf. There are two distinct groups that I am counting for. When implementing for one group, yours worked well on the larger data set that I have, but when implemented for two groups, I get no visualization, unfortunately.

MVP
MVP

Re: Count occurrences

Try this

Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 1), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 2), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 3), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 4), IDs)))


Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 1), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 2), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 3), IDs)),

Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 4), IDs)))


Capture.PNG

maelafifi
New Contributor III

Re: Count occurrences

This didn't quite give me the correct response, as it didn't scale to the number of ID's that I have in the actual app (17), so I made some tweaks and it seemed to work properly. I used a sum(wildmatch(),1), but still used the pick(match(valuelist())). Thank you very much.

Community Browser