Hi Team,
We are using cognos 10 version, In one of the dataitem i created some case statement...
case condition then 'x' else '' end ---- working
case condition1 then 'x' condition2 then 'x' condition3 then 'x' else '' end ---- not working.
Can someone guide me, not sure what i am missing here.....
TIA Team.
Regards,
Krish
Quote from: krishdw85 on 22 Jul 2024 07:16:17 AMHi Team,
We are using cognos 10 version, In one of the dataitem i created some case statement...
case condition then 'x' else '' end ---- working
case condition1 then 'x' condition2 then 'x' condition3 then 'x' else '' end ---- not working.
Can someone guide me, not sure what i am missing here.....
TIA Team.
Regards,
Krish
Hi,
Can you provide details on what you mean by "not working"? Do you get an error (and if so, what error)? Do you get an incorrect result (and if so, what is the result and what do you expect to get)? Do you get no result? Something else?
Can you also detail what the conditions are?
Cheers!
MF.
I mean when the condition is not met it has to display else'' that is blank. But it is somehow showing data (Incorrect result). Below is an example. Thanks for your quick response.
case
when jan then 'Y'
when feb then 'Y'
when mar then 'Y'
else '' end
For April also i see data as Y, generally it should be blank.
Quote from: krishdw85 on 22 Jul 2024 08:54:30 AMI mean when the condition is not met it has to display else'' that is blank. But it is somehow showing data (Incorrect result). Below is an example. Thanks for your quick response.
case
when jan then 'Y'
when feb then 'Y'
when mar then 'Y'
else '' end
For April also i see data as Y, generally it should be blank.
Hi,
That looks to be an incomplete simple case statement? For this type of Case statement, you need to focus on an item, eg
case [Month]
when 'Jan' then 'Y'
when 'Feb' then 'Y'
when 'Mar' then 'Y'
else ''
end
The alternative complex Case statement would be
case
when [Month] = 'Jan' then 'Y'
when [Month] = 'Feb' then 'Y'
when [Month] = 'Mar' then 'Y'
else ''
end
Your expression needs to conform to one of these patterns.
Cheers!
MF.
simple case statement, still issue....
case
when [P] ='GB' and
[EM] starts with 'M50M'
then 'Y'
when [P] ='SA' and [Z] ='ACC' or
[PR] starts with 'POT'
then 'Y'
when [P] ='CH'
and [Z] = 'ACC'
or [PR] starts with 'E1647'
or [PR] starts with 'E190'
then 'Y'
when [P] ='VT'
And [Z] = 'ACC'
or [PR] starts with 'POT'
or [PR] starts with 'ASS'
then 'Y'
when [P]= 'TR'
And [PR] ='A043'
Or [PR] = 'AS44'
Or [PR] starts with 'E19010'
Or [PR] starts with 'ASS'
then 'Y'
when [P]='BR'
and [PR] starts with 'ASS'
then 'Y'
when [P]='CI'
and [PR] ='ASS08' or
[PR]='ASS0' or
[PR]='ASS03'
then 'Y'
when [P]='UA'
and [PR] starts with 'ASS'
then 'Y'
else ' '
end
Quote from: krishdw85 on 25 Jul 2024 07:12:14 AMsimple case statement, still issue....
case
when [P] ='GB' and
[EM] starts with 'M50M'
then 'Y'
when [P] ='SA' and [Z] ='ACC' or
[PR] starts with 'POT'
then 'Y'
when [P] ='CH'
and [Z] = 'ACC'
or [PR] starts with 'E1647'
or [PR] starts with 'E190'
then 'Y'
when [P] ='VT'
And [Z] = 'ACC'
or [PR] starts with 'POT'
or [PR] starts with 'ASS'
then 'Y'
when [P]= 'TR'
And [PR] ='A043'
Or [PR] = 'AS44'
Or [PR] starts with 'E19010'
Or [PR] starts with 'ASS'
then 'Y'
when [P]='BR'
and [PR] starts with 'ASS'
then 'Y'
when [P]='CI'
and [PR] ='ASS08' or
[PR]='ASS0' or
[PR]='ASS03'
then 'Y'
when [P]='UA'
and [PR] starts with 'ASS'
then 'Y'
else ' '
end
Where you have conditions that use both AND and OR, the AND usually takes precedence. So for example
when [P] ='CH'
and [Z] = 'ACC'
or [PR] starts with 'E1647'
or [PR] starts with 'E190'
then 'Y'
would be evaluated as
([P] = 'CH' and [Z] = 'ACC') or ([PR] starts with 'E1647') or ([PR] starts with 'E190')
This would then return a TRUE result for any of the groups in parentheses - so any row where [PR] starts with 'E1647' (for example) would return a Y result from the case statement.
My guess is that you want this only to happen when [P] = 'CH' in this instance?
You should include your own parentheses to dictate how you want the conditions to be evaluated here.
You might end up with something like this:
case
when [P] ='GB' and
[EM] starts with 'M50M'
then 'Y'
when [P] ='SA' and ([Z] ='ACC' or
[PR] starts with 'POT')
then 'Y'
when [P] ='CH'
and ([Z] = 'ACC'
or [PR] starts with 'E1647'
or [PR] starts with 'E190')
then 'Y'
when [P] ='VT'
And ([Z] = 'ACC'
or [PR] starts with 'POT'
or [PR] starts with 'ASS')
then 'Y'
when [P]= 'TR'
And ([PR] ='A043'
Or [PR] = 'AS44'
Or [PR] starts with 'E19010'
Or [PR] starts with 'ASS')
then 'Y'
when [P]='BR'
and [PR] starts with 'ASS'
then 'Y'
when [P]='CI'
and ([PR] ='ASS08' or
[PR]='ASS0' or
[PR]='ASS03')
then 'Y'
when [P]='UA'
and [PR] starts with 'ASS'
then 'Y'
else ' '
end
Cheers!
MF.
Thanks so much! Issue is fixed.
Quote from: krishdw85 on 26 Jul 2024 10:37:48 AMThanks so much! Issue is fixed.
Great news! Thanks for updating us!