[SOLVED] Help understanding a DECODE...or any solution
Here's my situation. I have one fex that has a list of regions and their associated target values. Example:
TABLE FILE FIRST PRINT TARGET BY REGION END
What I want to do is take this information and use it in another fex - linking on the common column 'REGION' and dipslaying the appropriate TARGET along with other data. EXMPLE
TABLE FILE SECOND PRINT DATA TARGET BY REGION END
I am not able to do a JOIN because my data is all coming from OLAP cubes and I've been told that you cannot JOIN things to cubes. I've been told to use a DECODE but I am having a hard time seeing HOW to do that. If my FIRST file only has one row of data I can get this to work by doing a SAVE on my FIRST and then doing a READ to get the data for my SECOND and setting an IF statement to put it in the right place.This message has been edited. Last edited by: <Meghan>,
December 05, 2008, 02:38 PM
jimster06
Here is one of the things I found using the ECL facility with DECODE FILE as an argument
Or you can Decode against an external file -********************
Content of TEST1.TXT contains 'ENGLAND' 'XYZ' 'FRANCE' 'ABC'
Within your FOCEXCE :
FILEDEF TEST1 DISK C:\IBI\APPS\IBISAMP\TEST1.TXT DEFINE FILE CAR BBB/A3 = DECODE COUNTRY (TEST1 ELSE 'NNN'); END
TEST1 could be constructed:
TABLE FILE FIRST PRINT TARGET BY REGION ON TABLE HOLD AS TXFILE FORMAT ALPHA END You will need a FILEDEF etc HTH
jimster06 DevStu WF 7.6.11 W7 HTML, PDF, EXL2K
December 05, 2008, 05:05 PM
<Meghan>
Thank you jimster06. I was able to get this to work and display the target.
Next question - how can I do math on that target. My data now looks like this:
Region, Target, Score
I want to add another column that looks at the difference beetween target and score. I tried to add a compute
IF Score GE Target THEN 1 ELSE 0;
But I keep getting this error - FOC11307 Verb object should be one of the measure fields.
I'm assuming it has to do with the fact that this is a cube data source but I need to be able to do this. Any suggestions?
December 05, 2008, 05:34 PM
Darin Lee
Should be pretty straightforward:
COMPUTE DIFF/I1=IF SCORE GE TARGET THEN 1 ELSE 0;
You could also do that in a DEFINE. The type of data source shouldn't make any difference. Maybe you could post your code so we can see what you've got so far.
Regards,
Darin
In FOCUS since 1991 WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex WF Client: 77 on Linux w/Tomcat
December 05, 2008, 05:41 PM
<Meghan>
I've tried that and I get the verb object error. Here is my code
DEFINE FILE CORE_MEASURES_WITH_COMPARATORS
MeasureID/A3=EDIT(Comparator_Measure, '999');
Region/A14=
IF Comparator_Location1 EQ 'Lower Columbia Region (LCR)' THEN 'Lower Columbia' ELSE
IF Comparator_Location1 EQ 'Oregon Region (PHOR)' THEN 'Oregon' ELSE
IF Comparator_Location1 EQ 'Siuslaw Region (SIU)' THEN 'Siuslaw' ELSE
IF Comparator_Location1 EQ 'Southeast Alaska Region (SEA)' THEN 'SE Alaska' ELSE
IF Comparator_Location1 EQ 'Systemwide' THEN 'FOC_NONE' ELSE
IF Comparator_Location1 EQ 'Whatcom Region (WHA)' THEN 'Whatcom';
END
TABLE FILE CORE_MEASURES_WITH_COMPARATORS
PRINT
ComparatorValue AS 'Target'
BY Region
WHERE MeasureID EQ '008';
WHERE Comparator_Pillar1 EQ 'Safe and Clinically Effective Care';
WHERE Comparator_Time_Period1 EQ 'FY2009';
WHERE Comparator_Type1 EQ 'Operational Planning Grid - Target';
ON TABLE NOTOTAL
ON TABLE HOLD
END
-RUN
DEFINE FILE CORE_MEASURES_WITH_COMPARATORS
Value/D12.1% = DECODE Region(HOLD ELSE '');
END
SET KEEPDEFINE = ON
-INCLUDE app/coremeasures_defines_NEW.fex
TABLE FILE CORE_MEASURES_WITH_COMPARATORS
PRINT
Percent_of_Qualifying_Accounts/D12.1% AS Score
COMPUTE TargetIcon/A200 = IF Percent_of_Qualifying_Accounts GE Value THEN MeetTargetIcon ELSE NotMeetTargetIcon;
BY Region
BY Value
WHERE Measure_Set EQ 'Composite Quality Index - Heart Attack (AMI)';
END
December 06, 2008, 05:15 AM
<JG>
You don't say which OLAP you are going against, but your best option is to create a hold file and do your define and computes against that. it's much more efficient and removes the OLAP engine restrictions.
December 06, 2008, 11:38 AM
FrankDutch
If you want to do a decode from a temporary file you have to create that file with spaces and quotes around the values. Your region field has spaces.... and might look like this. (I have no idea what the field ComparatorValue might look like)
Lower Columbia abcde
Oregon aabde
Siuslaw jdsks
Whatcom kskso
Now if you do a decode, the system does "think" your first value is "Lower" and the decoded value is "Columbia" then the next ???
And I'm afraid the FOC_NONE will give you some trouble too.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
December 08, 2008, 12:53 PM
<Meghan>
Frank,
How do I create the temporary file with spaces and quotes around the values?
It's odd - when I DO NOT name my HOLD file the decode works but when I try to name my HOLD file (which I'll need to do) it fails and I think it's getting hung up, as you say it will, on the space between Lower and Columbia. I'm thinking that you're suggestion on putting quotes around my fields would fix this but again I do not know how to do this.
December 08, 2008, 02:13 PM
jimster06
Meghan- Frank might be asleep at this hour so let me attempt to answer your question. the DECODE function works on code/result pairs that are sparated by a space. If there is a space in the value of the code or result, WF is going to be disoriented. One way to avoid this problem is to put the value of each item within quote signs. DEFINE TABLE MYFILE MYRESULT/Ax = '''RESULT '''; END TABLE FILE MYFILE PRINT CODE MYRESULT ETC
When WF sees ''' it resolves it to ' and your decode table stays recognizable.
HTH
jimster06 DevStu WF 7.6.11 W7 HTML, PDF, EXL2K
December 08, 2008, 02:19 PM
susannah
quote:
Value/D12.1% = DECODE Region(HOLD ELSE '');
ha! frank never sleeps Meghan one of the problems i see (which may have no impact at all) is that the ELSE condition in your DECODE is Alpha, whereas your Value format is numeric. and make sure somewhere early on you have SET ASNAMES = ON
FYI your decode could be written
Region/A14=DECODE Comparator_Location1 ('Lower Columbia Region (LCR)' 'Lower Columbia'
'Oregon Region (PHOR)' 'Oregon'
'Siuslaw Region (SIU)' 'Siuslaw'
'Southeast Alaska Region (SEA)' 'SE Alaska'
'Whatcom Region (WHA)' 'Whatcom' ELSE 'FOC_NONE');
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
December 08, 2008, 02:42 PM
<Meghan>
Jimster -
Thank you! This works. Everything functions exactly as planned.
Thanks everyone for your help. I'll post my final code in a second for future reference.
December 09, 2008, 10:43 AM
<Meghan>
Here is my final code with two HOLD files and then another fex that DECODES the values from these HOLD files.
-INCLUDE app/Comparators_Defines.fex TABLE FILE CORE_MEASURES_WITH_COMPARATORS PRINT ComparatorValue BY Region WHERE MeasureID EQ '&Measure'; WHERE Comparator_Pillar1 EQ 'Safe and Clinically Effective Care'; WHERE Comparator_Time_Period1 EQ 'FY2009'; WHERE Comparator_Type1 EQ 'Operational Planning Grid - Target'; ON TABLE NOTOTAL ON TABLE HOLD AS FYTARGET FORMAT ALPHA END -RUN -INCLUDE app/Comparators_Defines.fex TABLE FILE CORE_MEASURES_WITH_COMPARATORS PRINT ComparatorValue BY Region WHERE MeasureID EQ '&Measure'; WHERE Comparator_Pillar1 EQ 'Safe and Clinically Effective Care'; WHERE Comparator_Time_Period1 EQ 'FY2007 - FY2012'; WHERE Comparator_Type1 EQ 'Vision 2012 - Target'; ON TABLE NOTOTAL ON TABLE HOLD AS VTARGET FORMAT ALPHA END -RUN
DEFINE FILE CORE_MEASURES_WITH_COMPARATORS FYTarget/D12.1% = DECODE Region(FYTARGET ELSE 0); VTarget/D12.1% = DECODE Region(VTARGET ELSE 0); DummyCode/A26 = IF FYTarget EQ '' THEN 'NA' ELSE FTOA(FYTarget, '(D12.1%)', 'A26'); Calculation/A200 = IF Percent_of_Qualifying_Accounts GE FYTarget THEN '' ELSE ''; END SET KEEPDEFINE = ON -INCLUDE app/coremeasures_defines_NEW.fex TABLE FILE CORE_MEASURES_WITH_COMPARATORS PRINT Percent_of_Qualifying_Accounts/D12.1% AS Score BY Region BY FYTarget BY VTarget WHERE Measure_Set EQ 'Composite Quality Index - Heart Attack (AMI)'; END