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 if data is present in any of n fields

Hi,

I have some data where people have qualifications listed in two fields:

Name
Qualification 1Qualification 2Qualification ......Qualification N
AliceAB...C
BrianBC...X
CharlesCA...Z
DaveAD...W
EmilyDF...A
FrankFX...C
GaryFZ...J
HarryGW...K
IvanWS...R
JamesHB...Q

Qualifications are added from a set list which is the same regardless of if the data is in "Qualification 1, 2 or N". These fields are only there as qaulifications are added as they are gained in time.

I need to return a count of "how many people have each qualification?", which in the above example for W is 3. (Ivan, Harry & Dave) or for A is 4 (Alice, Charles, Dave & Emily).

Data in each column is unique, ie Emily cannot have qualifications A,B,...,A. (or using this data example, not more than 26 qualifications if the pick list for quals is A to Z).

Help welcome please!

2 Replies
Anonymous
Not applicable
Author

Just realised first line says data is in 2 fields: it is in my current project but I need to know how to expand for n fields for other projects too.

Quy_Nguyen
Specialist
Specialist

Hi,

Try crosstable function to transform it to:

  

NameQualificationTypeValue
AliceQualification 1A
AliceQualification 2B
AliceQualification NC
BrianQualification 1B
BrianQualification 2C
BrianQualification NX

Ex:

CrossTable(QualificationType, Value,1)

Load

Name,

Qualification1,

Qualification2,

..

QualificationN

From XXX;

Then expression to count how many people have qual A:  Count({<Value = {"A"}>}Name)