Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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

Anonymous
Not applicable
Author

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.