Case Statement with Multiple Outcomes

[stanleytheyak]

I currently have a filter that looks like this:

[Activities].[Notes].[Comments]contains 'Didn''t' or
[Activities].[Notes].[Comments]contains 'didn''t' or
[Activities].[Notes].[Comments]contains 'Did not' or
[Activities].[Notes].[Comments]contains 'did not' or
[Activities].[Notes].[Comments]contains 'Failed' or
[Activities].[Notes].[Comments]contains 'failed' or
[Activities].[Notes].[Comments]contains 'Late' or
[Activities].[Notes].[Comments]contains 'late'

And I now have a case statement that will return the first word it encounters.  The case statement looks like this:

case
when([Comments]contains 'Didn''t') then 'Didn''t'
when([Comments]contains 'didn''t') then 'didn''t'
when([Comments]contains 'Did not') then 'Did not'
when([Comments]contains 'did not') then 'did not'
when([Comments]contains 'Failed') then 'Failed'
when([Comments]contains 'failed') then 'failed'
when([Comments]contains 'Late') then 'Late'
when([Comments]contains 'late') then 'late'
else ''
end

This will return ONE word, but I'd like it to return any of the words that are in the comments.  So if the comments contain"didn't" and "late", I would like to see "didn't, late" in the newly created field.

HELP!?

[anjidwh.cognos]

Hi ,

Create a separate query item for each case stmt and then keep all query items in another query item join with concatenation operator and separated by comma.


Thanks
SR

[stanleytheyak]

I tried this.  The problem is that if one of the keywords is NOT in the comments, the concatenation shows as blank.