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!