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>,

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

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?

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.

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   

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.

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,

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.

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

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');

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.

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