Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
KristapsR
Contributor
Contributor

COUNTIFS Equivalent in Data Load Script

I am building my first QlikSense app, and need help creating a COUNTIFS formula in my data load script. I've tried looking this up online, but none of the results seem to help me. 

To start, I have two tables.

USERS:

NAME
Anna
John
Mike
Peter

 

GROUPS:

GROUP NAME
Admin John
User Anna
User John
User Peter
User Peter
Manager Mike

   

I'm looking to find a COUNTIFS-equivalent formula to create a new calculated field in my data load script. It needs to count if each user from the USERS table is a member of either Admin or User group.

The final output, using the above examples, should look like this:

NAME COUNTIFS(User+Admin) =  COUNTIF(User) +  COUNTIF(Admin)
Anna 1 1 0
John 2 1 1
Mike 0 0 0
Peter 2 2 0


How can it be done? Or more importantly, CAN it be done? 

4 Replies
rubenmarin

Hi, you can use a sum if, like:

LOAD
  NAME,
  Sum(If(Match(GROUP,'User','Admin'),1)) as [User+Admin],
  Sum(If(GROUP='User',1)) as User,
  Sum(If(GROUP='Admin',1)) as Admin
Resident Table1
Group By NAME;
KristapsR
Contributor
Contributor
Author

Thanks! Just tried it, and it does not find my GROUP field from the GROUPS table 😕 Would I need to do something like a JOIN between the two tables I have, USERS and GROUPS? 

 

The following error occurred:
Field 'GROUP' not found

 

 

mfarsln
Creator II
Creator II

If you have two different tables then you have to join or map these two tables first. Since you need only one column from one table, mapping seems better for your situation.

rubenmarin

Hi, for this particular question you only need the GROUPS table, it already  has NAME and GROUP.