If you are unable to create a new account, please email support@bspsoftware.com

 

How to avoid a duplicate extra blank row when using Case When function?

Started by CMWY0529, Yesterday at 08:37:44 PM

Previous topic - Next topic

CMWY0529

Hello All,

Quick question about a customized data item in my Cognos report that uses Case When function, the logic is as below:

CASE WHEN (Field A = 'Value 1')
THEN 'Pass'
END

For this data item, I wanted to look for field A which could have multiple values entered, Value 1, Value 2, Value 3, etc... and only those records that have their field A with an entry of Value 1, would display a "Pass" in the output. However, I realized that without specifically defining an ELSE condition in the Case When statement, it defaults as "ELSE NULL" so for those records with Value 1 in field A, it generates an extra blank row in the output, like this.

Record Number   Field A Yes/No
Record 1       
Record 2
Record 3        Pass
Record 3
Record 4
Record 5        Pass
Record 5

Because for Record 3, and Record 5, they both had multiple data entries into field A and only one entry was Value 1, so the Case When function checks Value 1 and outputs a Pass as a row, but then because there's also Value 2 so it outputs an extra blank row. My question is, how do I modify the logic to avoid generating the extra rows for Record 3 and Record 5?

First post here and thank you for your help in advance!