I have a report that gathers data from our payroll system and they are renaming the paycodes. Ex: PER (Personal) is becoming PTO (Personal time off). But... There is always a but, they want the report to stay the same.

I piped up at the meeting right away and said it could be done in webfocus. I had seen it done before.

Now I can not find it for the life of me and I don't even now what to search for. "Replace" returns to many results, and I can not search for "AS" its to short.

Its on the tip of my tongue so to speak.

So Funeral Leave (FNL), is becoming 30 different Bereavement codes. B01, B02, B03, etc... Depending on the relation to you of the person that died.

So If my report sees and field that is EQ B* I need the report to still show FNL.

IF PAYCODE EQ 'B01 OR B02 OR B03' PRINT AS FNL <--- is this close?

I know this is easy, I just can't find an example.

This message has been edited. Last edited by: Greg,

DEFINE FILE CAR
NEWNAME/A30=DECODE COUNTRY('FRANCE' 'ROQUEFORT' 'ITALY' 'PARMIGIANA' 'JAPAN' 'SAKI' 'ENGLAND' 'STILTON' ELSE 'AMERICAN');
END
TABLE FILE CAR
SUM SALES BY NEWNAME
END
...or...
DEFINE FILE CAR
NEWNAME/A30=IF COUNTRY EQ 'ITALY' OR 'FRANCE' OR 'ENGLAND' THEN 'EUROPEAN UNION' ELSE
IF COUNTRY EQ 'GREECE' THEN 'JURY STILL OUT' ELSE 'USA');
END
you can string up to 41 IF THEN ELSE's together, as i recall, before you have to start a new one.

More on the DECODE function... If the conversion will be used in a number of reports, you could place the DECODE elements in a flat file, or a separate FEX (and use -INCLUDE) so you have only one place to do maintenance. Depending on the number of conversion elements (DECODE has limits), you might need to create a FOCUS file indexed on the field containing the new values and JOIN to this table to pick up the legacy values.

This message has been edited. Last edited by: Dan Satchell,

amen to that, Dan

The report in question is a pdf compound report of 5 different reports. So DECODE in a flat file may be the way to go.

The flat file is the easiest to maintain if you don't go beyond the limits.

Building off of Susannah's example above couldn't you just use:

 
DEFINE FILE CAR
NEWNAME/A30=DECODE COUNTRY(
ITALY 'EUROPEAN UNION'  
FRANCE 'EUROPEAN UNION' 
ENGLAND 'EUROPEAN UNION' 
GREECE 'JURY STILL OUT'
ELSE USA);
END

Quotes only needed if there are spaces.
I find it easier because I don't have to have all those OR's and I can list them one line at a time so it's easy to edit. In addition, you can put a define in a Masterfile so that it applies to any reports that use that Masterfile. Of course it's not as flexible as the if statements because it's only exact matches so you couldn’t do the following:

 
NEWNAME/A30= IF COUNTRY CONTAINS 'E' OR 'Y' THEN 'EU' ELSE 'USA';

Which might make it easier on all those B* codes.