Converting Day of Week Abbreviated Values

[wwconslt]

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.

[Lynn]

Substring, case, and concatenate are your best friends  ;)

Code Select Expand


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.

[wwconslt]

Thank you.  That worked perfectly!