I am trying to get the distinct count of ID column based on condition on category column; for example in tableau the condition is written like this:-

  IF [Status] = "COMPLETE" THEN COUNTD([Survey ID]) ELSE 0 END)

I tried in Cognos (IBM cloud pak for data)  like this:- 

count distinct (IF ( cognos_1_csv.Status='COMPLETE' ) THEN ( cognos_1_csv.Survey ID ) end)

I am getting errors , Could any one suggest how to write this in Cognos.

------------------------------
Sunita Alwani
------------------------------

#CognosAnalyticswithWatson

Hi Sunita, 

You can use either Case or IF. IF in Cognos requires an Else part also where as Else is optional in Case statement. So, you can try below options based on your need. 

IF ( cognos_1_csv.Status='COMPLETE' ) THEN (count(distinct cognos_1_csv.Survey ID )) ELSE (0)
CASE WHEN cognos_1_csv.Status='COMPLETE' THEN (count(distinct cognos_1_csv.Survey ID )) ELSE (0) END

Regards,

------------------------------
Kiran Passumarthi
www.linkedin.com/in/kiranpassumarthi
------------------------------

Original Message:
Sent: Wed October 06, 2021 07:55 AM
From: Sunita Alwani
Subject: Count Distinct based on if condition

I am trying to get the distinct count of ID column based on condition on category column; for example in tableau the condition is written like this:-

  IF [Status] = "COMPLETE" THEN COUNTD([Survey ID]) ELSE 0 END)

I tried in Cognos (IBM cloud pak for data)  like this:- 

count distinct (IF ( cognos_1_csv.Status='COMPLETE' ) THEN ( cognos_1_csv.Survey ID ) end)

I am getting errors , Could any one suggest how to write this in Cognos.

------------------------------
Sunita Alwani
------------------------------

#CognosAnalyticswithWatson

Hi Sunita,
please use one of the following:

count ( distinct    case when Cognos_1_csv.Status = 'COMPLETE' then cognos_1.csv.Survey_ID end )

or

count ( distinct    if ( Cognos_1_csv.Status = 'COMPLETE' ) then (cognos_1.csv.Survey_ID ) else (null) )
 
You want to do the count ( distinct .. operation on the outside

Please check the spelling of Survey_ID, in your example it had a space in it.

------------------------------
Kind regards,
HENK CAZEMIER
------------------------------

Original Message:
Sent: Wed October 06, 2021 07:55 AM
From: Sunita Alwani
Subject: Count Distinct based on if condition

I am trying to get the distinct count of ID column based on condition on category column; for example in tableau the condition is written like this:-

  IF [Status] = "COMPLETE" THEN COUNTD([Survey ID]) ELSE 0 END)

I tried in Cognos (IBM cloud pak for data)  like this:- 

count distinct (IF ( cognos_1_csv.Status='COMPLETE' ) THEN ( cognos_1_csv.Survey ID ) end)

I am getting errors , Could any one suggest how to write this in Cognos.

------------------------------
Sunita Alwani
------------------------------

#CognosAnalyticswithWatson