if then

[cognosmanager]

Hi All,

I have a report in which i have Name, Column A, Column B, Column C and Date.

Name        Column A       Column B        Column C                  Date
XYZ              250                                        265                   Dec 31 2011   
XYZ               0                                           196                  June 30 2011

And made Query Calculation column in which the condition is "if ((to_char ([Date],'mm/dd') in ('12/31') and [Column A] >0) and (to_char ([DATE],'mm/dd') in ('06/30') and [Column A] = 0)) then ([Column C]) else ([Column B])".

If both the conditions are satisfied then it should show Column C value but instead it is giving value from Column B.

Please help me out.

[wyconian]

Hi

Think you've gone a bit astray in your logic.  You calcualtion says if date = 12/31 and date = 06/30 then column c else column b

The date can't be both 12/31 and 06/30 which is why you're getting column B coming back.

Try something like
(if (to_char([Date].'mm/dd') in ('12/31','06/30') and ([Column A] = 0) then ([Column C]) else ([Column B])

Good luck

[cognosmanager]

Hi,

What I want is that a person will be pulled if both the dates are satified i.e. for 31 dec 2011, column A should be greater than 0 and for 30 june 2011, column A should be equal to zero. If both of them are satisfied then value of Column C should come up.

[Lynn]

I'll take a shot at it...

Code Select Expand


case
  when to_char([Date],'mm/dd') in ('12/31') and [Column A] > 0
    then [Column C]
  when to_char([DATE],'mm/dd') in ('06/30') and [Column A] = 0
    then [Column C]
  else [Column B]
end


[wyconian]

Cognos manager I understand what you're asking (I think), bear in mind that the filter will be applied to each record in turn.  I would have thought that you can't have 1 record where the date is 12/31 and 06/30 (it could be one or the other but not both), that's why you were getting the wrong column coming back.

Lynne's calc does the same thing as mine (I think) but is probably easier to read.

As an aside if you've only got one value to compare things to it's faster to do = than in.

[Lynn]

Filter??  :o

I think this is a query calculation that looks at the date column and column A....it then returns either column C or B depending on the conditions.

A single date column cannot be two values on the same row, but it can contain different values on different rows. The case statement considers two conditions that result in column C being selected and uses column B for any other condition.

Wyconian, your expression says column A must be *equal* to zero for both dates, but the requirement (I think) is for A to be greater than zero for 12/31 and equal to zero for 6/30.

Signed,
Lynn (without an "e")  ;)

[wyconian]

Sorry Lynn I missed that :)

[cognosmanager]

Thanks for the help guys.

[cognostechie]

Thanks for the help guys.

You mean thanks for the help wyconian and Lynn ?  ;D

[Lynn]

Too funny!  ;D

I say "guys" all the time without regard to gender of those in the group I am addressing. I haven't encountered any women who are bothered by this.

I wonder what would happen if I started saying "gals" all the time instead?  ;)

[MFGF]

I wonder what would happen if I started saying "gals" all the time instead?  ;)

I would assume it was the weekend if you were referring to me :)

[jive]

Hi , instead of havin an 'AND' in your if-then-else replace it by a 'OR'  like

"if ((to_char ([Date],'mm/dd') in ('12/31') and [Column A] >0) OR (to_char ([DATE],'mm/dd') in ('06/30') and [Column A] = 0)) then ([Column C]) else ([Column B])".

And it will work