IBM Support

Performing a Count Distinct for a column based on another column

Troubleshooting


Problem

How can you perform a Count Distinct for a column based on another column? Example: You have a column that has Employee ID's in it where there are multiple entries for each employee. You have another column that has an Active status flag and the goal is to see how many employees have an active flag.

Resolving The Problem

Use a calculated column that does a count distinct on the Employee ID's and then build a filter to only bring back the records that have the active flag.

Another alternative is to use the following in a calculated column:

count (distinct if (<active flag column> = 'Y' ) then (<Employee ID column>) else NULL )

[{"Product":{"code":"SSTQPQ","label":"Cognos Series 7"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Impromptu","Platform":[{"code":"PF025","label":"Platform Independent"},{"code":"PF033","label":"Windows"}],"Version":"Cognos 8 BI Framework Manager 8.1;Cognos 8 BI Query Studio 8.1;Cognos 8 BI Query Studio 8.2;Cognos ReportNet 1.1MR2;Cognos ReportNet 1.1MR3;Impromptu 5.0","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Framework Manager","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SS9RTN","label":"Cognos 8 Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Query Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSQJXN","label":"Cognos ReportNet"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"ReportNet","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSTQPQ","label":"Cognos Series 7"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Impromptu","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}}]

Historical Number

80074

Document Information

Modified date:
15 June 2018

UID

swg21348942