Maximum Value for character Datatype

[BiliBoy]

Hello Members

i have sets of 5 values, PK,KG,01,02,03. Now i want choose PK as lowest value and 03 as a Highest value. How can i do that with maximum or other function in cognos? Datatypes for all values is character, no numeric.  anybody willing to help me?

[cognostechie]

Ask yourself why do you want to do that? Did your business tell you so? If so, what's the business rule behind this?

Technically, you can create a key (another column that will act as a sort key) with a Case statment or IF ..then.. Else condition and say:

Case
  when value = 'PK' then 1
  when value = '<whatever>' then 2
  when value = '<whatever>' then 3
  when value = '<whatever>' then 4
  when value = '03' then 5
End                                                    as 'Sort Key'

.. then apply this sort key to either sort or use this sort key in another calculated column to determine which one is lower vs higher.

However, this is hard coding and would be unstable and require maintenance so the best thing is to determine the logic first intead of judt doing whatever they asked you to do for now.

[BiliBoy]

Thanks Cognostechie, i have already  implemented the idea you suggested me in my other report but i am building a report which eventully i  will be converting in to MS access table so i do not know wheather aggregation will be carry forward or not. Thanks for the idea though.