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":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"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":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Framework Manager","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Query Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SUNSET","label":"PRODUCT REMOVED"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"ReportNet","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSTQPQ","label":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Impromptu","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
80074
Was this topic helpful?
Document Information
Modified date:
28 November 2022
UID
swg21348942