Hi all, we have a very dirty table here that I need to clean up and make presentable with the help of Cognos.
The column that I'm working with is State, and this is free-form - which means that we get both state codes (MA, NY, CA) and state names (Massachusetts, New York, California) in this column.
Now, I essentially want to do a case statement that finds all full state names and replaces them with the state code for the report.
It would be cumbersome to do it in the report and I have a DB table with both names and codes, how would you do it? I dabbled in Parameter Maps but I wasn't able to make head or tail of it - any help would be appreciated.
Original State Column
| Modified State Column
|
Why not create an Xref table that has the two columns (original and modified). Then do a relationship on the original that allows multiple in your table to 1 in the lookup table. Then output the modifed column in your Business View as the State Data Item.
Original Modified
TN TN
Tennessee TN
FL FL
Florida FL
etc.....
Have your table point to the XREF table with join on "Original to State"... then pull modified into your Business View... Make sense?
Why would you want to do this at run-time in a report? ...why not just run a regular UPDATE and cleanse that data?