Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
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
Highlighted

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

View solution in original post

5 Replies
Highlighted
Contributor III
Contributor III

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

Highlighted

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

Highlighted
Contributor III
Contributor III

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.

Highlighted

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

View solution in original post

Highlighted
Contributor III
Contributor III

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.