Column calculation in Transformer

[RNRM]

Cognos series 7 WinXP Pro
Hi,

I am needing to enter the following calculation into transformer (by inserting column into the data source pane):

if ("Score" < 50) then ('0-49(G)') else if ("Score" >= 50 and <= 99) then ('50-99(G)') else if ("Score" >= 100 and <= 124) then ('100-124(G)') else if ("Score" >= 125 and <= 149) then ( '125-149(F)') else if ("Score" >= 150 and <= 159) then ('150-159(E)') else if ("Score" >= 160 and <= 169) then ('160-169(C)') else if ("Score" >= 170 and <= 179) then ('170-179(C)') else if ("Score" >= 180 and <= 189) then ('180-189(B)') else if ("Score" >= 190 and <= 199) then ('190-199(B)') else if ("Score" >= 200 and <= 224) then ('200-224(B)') else if ("Score" >= 225 and <= 249) then ('225-249(B)') else if ("Score" >= 250 and <= 280) then ('250-280(A)') else if ("Score" >= 281 and <= 299) then ('281-299(A)') else ('300+ (A)')

But I get the error: TR1112 'the function used in the expression is invalid' (not documented anywhere and only mentioned once on this forum with no productive response BTW)

This used to be a folder calculated column in the catalogue but that is no longer possible due to some changes.

I cannot post the folder column from the catalog because of Cognos's interesting developmental decision not to use the windows clipboard - making it impossible to cut and paste in the usual fashion. They way I had to do this in the end was to take a screenshot, paste it in to MS Paint (I kid you not) and refer back to it whilst 'clicking through the barely useable interface provided for entering these calculations... Incidentally the memory space that they DO use is unloaded when you close and re-open the catalogue, another nice touch which means you cannot cut & paste these calcs between catalogues:-/
I also notice that you are not able to simply type your calc in (in the absence of cut & paste) and you are forced to click on the options provided, annoying but ok ...if the functions provided were available accross the board but they arent - there are different funcs available in each different expression editor!

I am a newbie user and honestly I cannot understand how this tool can be considered a professional product, I am hoping that some guru out there will show me some clever tricks and I will realise that I have simply not understood how to use the tools provided because from where I am now its really not an acceptable way to have to work.

Please tell me I am missing something and that all of the above complaints are easily overcome in a simple, elegant way. Surely this is our aim as developers? To create simple, elegant processes??

 

[winkid1]

Is your variable return type set to text or numeric? It should be set to text.

 

[Jim2005]

For Logical operator AND, you should use
("Score" >= 50 and "Socre" <= 99)
instead of
("Score" >= 50 and <= 99).

The best way is to use BETWEEN operator as below in your case:
("Score" between 50 and 99).

 

[drlex]

And further, if you are doing logical tests in numerical sequence, you only have to test the upper limit:

if ("Score" < 50) then ('0-49(G)') else if ("Score" <= 99) then ('50-99(G)') else if ("Score" <= 124) then ('100-124(G)') .....

For cut and paste, if you are using IQDs as data sources (and are happy with SQL syntax), you can always use Notepad to add columns to the IQD and then 'Modify Columns' in Transformer to bring them in to the model.

soi la, soi carré