CODEWITHSUNDEEP
cognoscognos-10cognos-bi
MaZe
MaZe

Reputation: 259

Cognos query calculation - how to obtain a null/blank value?

I have a query calculation that should throw me either a value (if conditions are met) or a blank/null value.

The code is in the following form:

if([attribute] > 3) then ('value') else ('')

At the moment the only way I could find to obtain the result is the use of '' (i.e. an empty character string), but this a value as well, so when I subsequently count the number of distinct values in another query I struggle to get the correct number (the empty string should be removed from the count, if found).

I can get the result with the following code:

if (attribute='') in ([first_query].[attribute])) then (count(distinct(attribute)-1) else (count(distinct(attribute))

How to avoid the double calculation in all later queries involving the count of attribute?

Upvotes: 0

Views: 9888

Answers (2)

Tripanosomagambiense
Tripanosomagambiense

Reputation: 194

I use this Cognos function:

nullif(1, 1)

Upvotes: 2

MaZe
MaZe

Reputation: 259

I found out that this can be managed using the case when function:

case when ([attribute] > 3) then ('value') end

The difference is that case when doesn't need to have all the possible options for Handling data, and if it founds a case that is not in the list it just returns a blank cell.

Perfect for what I needed (and not as well documented on the web as the opposite case, i.e. dealing with null cases that should be zero).

Upvotes: 0

Related Questions