We have a field with data in a "current" and "legacy" format. For the report, we want to create a defined field that translates the legacy code to a description that would be compatible with a description we have defined for the current code. The logic is if the first character is "^" upcarat then look at the description otherwise use the 2 characters beginning at position 5. I was able to get it to display the "current" code in a defined field by itself and to display the translated "legacy" code by itself, but when I tried to combine them in one field, I did not get the results I'm expecting. I imagine that the logic may be missing something. I am not too familiar with all the functions, so the define may not be written the best way. Here is an example of what the result should be: -------------------------------- Code ==> Description 11S_ST_OD ==> ST ^05MMA MSEM_Manila ==> "MA" -------------------------------- Using the above example, the actual output of the report shows: CohortNew CohortOld CoCombined ST XX ST MA SF ------------------------ Line 2 CoCombined Should be MA not SF ------------------------
Here is the Define: COHORTNEW/A3=IF BYTVAL(COHORT, 'I3') NE 94 THEN EDIT(COHORT, '$$$$99$$$$$'); POSITS/I3=BYTVAL(COHORT, 'I3'); COHORTOLD/A30= IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Sacramento' THEN 'ST' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'SF' THEN 'SF' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'N Bay' THEN 'NB' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'S Bay' THEN 'SB' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'San Ramon' THEN 'SR' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'LA' THEN 'LA' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Manila' THEN 'MA' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Bangkok' THEN 'BK' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'Xiamen' THEN 'XI' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD) = 'New Orleans Refugee' THEN 'NO' ELSE 'XX'; CoCombined/A3= IF BYTVAL(COHORT, 'I3') NE 94 THEN EDIT(COHORT, '$$$$99$$$$$') ELSE IF BYTVAL(COHORT, 'I3') EQ 94 THEN IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Sacramento' THEN 'ST' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'SF' THEN 'SF' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'N Bay' THEN 'NB' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'S Bay' THEN 'SB' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'San Ramon' THEN 'SR' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'LA' THEN 'LA' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Manila' THEN 'MA' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Bangkok' THEN 'BK' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'Xiamen' THEN 'XI' ELSE IF GETTOK(COHORT_DESC, 100, -1, '_', 30, 'A3') = 'New Orleans Refugee' THEN 'NO'; ENDThis message has been edited. Last edited by: Kerry,
WebFocus 7.7.03 Win7, all output
February 09, 2011, 07:06 AM
OPALTOSH
You need an ELSE on the end of your defines and you caould simplify the code as follows.
COHORTNEW/A3=IF BYTVAL(COHORT, 'I3') NE 94 THEN EDIT(COHORT, '$$$$99$$$$$') ELSE '???'; POSITS/I3=BYTVAL(COHORT, 'I3'); TESTVAL/A3=GETTOK(COHORT_DESC, 100, -1, '_', 30, COHORTOLD); COHORTOLD/A30= IF TESTVAL EQ 'Sacramento' THEN 'ST' ELSE IF TESTVAL EQ 'SF' THEN 'SF' ELSE IF TESTVAL EQ 'N Bay' THEN 'NB' ELSE IF TESTVAL EQ 'S Bay' THEN 'SB' ELSE IF TESTVAL EQ 'San Ramon' THEN 'SR' ELSE IF TESTVAL EQ 'LA' THEN 'LA' ELSE IF TESTVAL EQ 'Manila' THEN 'MA' ELSE IF TESTVAL EQ 'Bangkok' THEN 'BK' ELSE IF TESTVAL EQ 'Xiamen' THEN 'XI' ELSE IF TESTVAL EQ 'New Orleans Refugee' THEN 'NO' ELSE 'XX'; CoCombined/A3= IF POSITS NE 94 THEN EDIT(COHORT, '$$$$99$$$$$') ELSE -* this is redunadntIF BYTVAL(COHORT, 'I3') EQ 94 THEN IF TESTVAL EQ 'Sacramento' THEN 'ST' ELSE IF TESTVAL EQ 'SF' THEN 'SF' ELSE IF TESTVAL EQ 'N Bay' THEN 'NB' ELSE IF TESTVAL EQ 'S Bay' THEN 'SB' ELSE IF TESTVAL EQ 'San Ramon' THEN 'SR' ELSE IF TESTVAL EQ 'LA' THEN 'LA' ELSE IF TESTVAL EQ 'Manila' THEN 'MA' ELSE IF TESTVAL EQ 'Bangkok' THEN 'BK' ELSE IF TESTVAL EQ 'Xiamen' THEN 'XI' ELSE IF TESTVAL EQ 'New Orleans Refugee' THEN 'NO' ELSE '???' ;
February 09, 2011, 07:24 AM
Wep5622
Even better, you can rewrite those IF THEN ELSE statements to use DECODE, like so:
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
and it gave me the required result. Hope this helps ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
February 09, 2011, 08:44 AM
George Patton
This looks way too complicated for what you are trying to do. For starters, why not take a look at the DECODE function to get rid of all of those IF THEN ELSE statements. You can then use the EDIT function to check for the upcaret and also to select the 5th and 6th characters.
Oops .... looks like Wep5622 and GamP beat me to it ....
Thank you all for your great help. I used GamP's code and it is working. This brought to light that there are some more "exceptions". I noticed a case where it did not translate a "San Ramon". I thought perhaps it had to do with the "^" have an "_" in the description, meaning that it picks up "_San Ramon", but not picking up " San Ramon". However, "New Orleans" does not have an "_" and it is picked up, so now I'm not sure what to think.
COHORT COHORT_DESC COHORTNEW COHORTOLD CoCombined ^NOLA New Orleans Refugee NO NO ^08SSR Clinic Nurse Lead San Ramon SR XX XX ^5FRAC EDUC_San Ramon SF SR SR
WebFocus 7.7.03 Win7, all output
February 09, 2011, 02:57 PM
sxschech
I overcame the issue by adding hard code values in order to get the description to show up. I tried to spell out the wording in the decode, but that didn't work, so I used an IF for the San Ramon. I left the wording in the decode in the snippet below, however, in case I missed something.
For readbility you can arrange your DECODE vertically, or even put the decode pairs in a separate file. You could create that file dynamically as well.