Good Morning,
I'm using Cognos 10.1.1 with relational data. What I'm hoping to do is convert abbreviated day of the week values to separate days. For example, I have a field called "Meeting Days" where the values could be something like what you see in the left column below where letters with dashes are currently being stored. I would like to have the result in a new data item such as what you see on the right where I strip out the dashes then have the full day of the week appear for each abbreviated letter
-M-W-F- Monday Wednesday Friday
--T---- Tuesday
---W--- Wednesday
Thank you for any help or suggestions.
Substring, case, and concatenate are your best friends ;)
case when substring ( [Meeting Days], 1, 1) = 'S' then 'Sunday' else ' ' end
||
case when substring ( [Meeting Days], 2, 1) = 'M' then 'Monday' else ' ' end
||
case when substring ( [Meeting Days], 3, 1) = 'T' then 'Tuesday' else ' ' end
...etc...
You will end up with extra blanks for days that don't have a value with something similar to the above suggestion. You could make your expressions a little more complicated by checking preceding and/or following values to suppress unnecessary blank spaces.
Thank you. That worked perfectly!