COUNTIFS Equivalent in Data Load Script

[KristapsR]

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? 

[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]

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]

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.