Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Conditional Count

Hello,

I'm using a (rather large) database in which the main ID can be repeated. I need to establish a condition only for a specific code (let's say, "D"), so that if such ID has any other code besides this one, it is not counted, but if the only time it shows it's with this code, it is counted. 

For example:

ID

Code

1

A

1

B

2

D

3

A

3

D

4

C

 

For ID 1, I have no problem counting it both for A and B. For ID 2, since its only code is D, it has to be counted. However, for ID 3, since it also has code A, I don't want it to be counted for D  : 

ID

Code

1

A

1

B

2

D

3

A

4

C

 

This can either be directly when counting the distinct codes or a transformation that gives a table such as the second one.

 

I appreciate the help.

1 Solution

Accepted Solutions
Highlighted
Luminary
Luminary

Re: Conditional Count

Try this script:

TableA:
LOAD
	ID,
	Code,
	IF(MATCH(Code, 'D'), '0', '1') AS Check
INLINE [
ID,Code
1,A
1,B
2,D
3,A
3,D
4,C];

TableB:
LEFT JOIN LOAD 
	ID,
	SUM(Check) AS CheckSum
RESIDENT TableA
GROUP BY ID;

TableC:
NOCONCATENATE LOAD * RESIDENT TableA 
WHERE MATCH(CheckSum, '0') OR NOT MATCH(CheckSum, '0') AND NOT MATCH(Code, 'D');
DROP TABLE TableA; DROP FIELDS Check, CheckSum;

View solution in original post

3 Replies
Highlighted
Luminary
Luminary

Re: Conditional Count

Try this script:

TableA:
LOAD
	ID,
	Code,
	IF(MATCH(Code, 'D'), '0', '1') AS Check
INLINE [
ID,Code
1,A
1,B
2,D
3,A
3,D
4,C];

TableB:
LEFT JOIN LOAD 
	ID,
	SUM(Check) AS CheckSum
RESIDENT TableA
GROUP BY ID;

TableC:
NOCONCATENATE LOAD * RESIDENT TableA 
WHERE MATCH(CheckSum, '0') OR NOT MATCH(CheckSum, '0') AND NOT MATCH(Code, 'D');
DROP TABLE TableA; DROP FIELDS Check, CheckSum;

View solution in original post

Highlighted

Re: Conditional Count

Is this something you want to change in the script or you want to may be use an expression which counts the ID?

Highlighted
Partner
Partner

Re: Conditional Count

This seems to have done the trick. Thanks a lot!