Hi Team,

I'm trying to do something like this:

COMPUTE FIELDNAME/D20.2=((IF...THEN...ELSE...) / (IF...THEN...ELSE...)) - ((IF...THEN...ELSE...) / (IF...THEN...ELSE...));

I wonder if this is even possible?

Normally I'd do four COMPUTES, a HOLD and then use the computed fields from the HOLD file in another COMPUTE, but for various reasons I have to do this in a single pass.

Ideas?

Cheers,

Joey

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

Should work.

But you can always unravel it as a series of COMPUTE's:

COMPUTE ALPHA/D12.2=IF ... THEN ... ELSE ...; NOPRINT
COMPUTE BETA /D12.2=IF ... THEN ... ELSE ...; NOPRINT
COMPUTE GAMMA/D12.2=IF ... THEN ... ELSE ...; NOPRINT
COMPUTE DELTA/D12.2=IF ... THEN ... ELSE ...; NOPRINT
COMPUTE FIELDNAME/D20.2=ALPHA/BETA - GAMMA/DELTA;

No intermediate HOLD needed.

Why not:

TABLE FILE BLAH
COMPUTE PART1/D20.2 = IF...THEN...ELSE;
COMPUTE PART2/D20.2 = IF...THEN...ELSE;
COMPUTE PART3/D20.2 = IF...THEN...ELSE;
COMPUTE PART4/D20.2 = IF...THEN...ELSE;
COMPUTE FIELDNAME/D20.2 = (PART1 / PART2) - (PART3 / PART4);
BY SOMETHING
END

The calculations will still happen in a single data pass but the code will be much easier to follow and debug.

You could try the all-in-one expression you suggested initially and actually see for yourself if WebFOCUS likes it

Oh man, timing! timing is always a funny thing!

win some, lose some.

As usual, hours behind the train, and the same idea.

You'll have to move to this side of the planet

On the other side, you'll have the evening posts all for yourself

Yes it gets a little lonely....

First, thank you for the excellent technique.

Sadly, I can't use this suggestion, and here's why. This is not so much a WebFOCUS issue as a PMF issue that involved FOCUS code. Here's the background:

PMF measures allow you to type FOCUS code when setting table and field mappings. So, you pick a data source, choose your dimensional keys to join on and then define the fields to use for Actual, Target, Forecast, Benchmark. Instead of choosing a particular table field, you can create a COMPUTE. However, anything that you want to map to a PMF field needs to happen in a single COMPUTE, which is why I can't use the technique you provided.

I think I dug my own hole on this one guys, and will have to tell my client I can't actually build this particular measure. Oh well.

Cheers,

Joey

How many combinations of tests are there in the If tests ?

Have you tried a very long IF THEN ELSE IF...

Will it be too large ?

Or...could you create a DEFINE function that you can call in your COMPUTE?

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

Hi again,

@Was: This has crossed my mind, but the issue is that I don't need the results of a nested IF statement so much as I need to compare the results of four IF statements against each other.

@Dan Satchell: I wondered about some kind of dblookup function, but... I do remember some tech memo about a define in a masterfile that does a query. Is there some kind of FOCUS function that can embed an entire TABLE request? Something like:

COMPUTE FIELDNAME/D20.2=(dblookup() / dblookup()) - (dblookup() / dblookup());

Essentially I need to bang everything into one request, although now that I think about it, I do have the capability to add fields to the masterfile. Hmmm..... I wonder about making four defines with the IF..THEN...ELSE... logic and then mapping to those fields as if they were real. This might work.

Cheers,

Joey

quote:

Have you tried a very long IF THEN ELSE IF...

Here's an extensive one:

-SET &RptNum = IF &RptType = 'ORGDETAIL' AND &STORE_DIV_NUM EQ 'FOC_NONE' AND ®ION_NUM EQ 'FOC_NONE' AND &DISTRICT_NUM EQ 'FOC_NONE' THEN 1 ELSE
-              IF &RptType = 'ORGDETAIL' AND &STORE_DIV_NUM NE 'FOC_NONE' AND ®ION_NUM EQ 'FOC_NONE' AND &DISTRICT_NUM EQ 'FOC_NONE' THEN 2 ELSE
-              IF &RptType = 'ORGDETAIL' AND &STORE_DIV_NUM NE 'FOC_NONE' AND ®ION_NUM NE 'FOC_NONE' AND &DISTRICT_NUM EQ 'FOC_NONE' THEN 3 ELSE
-              IF &RptType = 'ORGDETAIL' AND &STORE_DIV_NUM NE 'FOC_NONE' AND ®ION_NUM NE 'FOC_NONE' AND &DISTRICT_NUM NE 'FOC_NONE' THEN 4 ELSE
-              IF &RptType = 'DEPTDETAIL'                                                                                            THEN 5 ELSE
-              IF &RptType = 'DRILL'     AND &DrlFrm        EQ 'ORG'      AND &RptNum     LE 8                                       THEN &RptNum + 1 ;

OR:

-IF THIS1 EQ THAT1 THEN 1 ELSE
-IF THIS2 EQ THAT2 THEN 2 ELSE
-IF THIS3 EQ THAT3 THEN 3 ELSE
-IF THIS4 EQ THAT4 THEN 4 ELSE
ETC ...

I think this is limited to 16.

Hi Team,

I went down the path that Dan Satchell suggested, and it seemed to be going well for a time. I built my four fields as DEFINES in the masterfile, and they work well. However, when I try to make DEFINES for dividing them by each other, I get some oracle error about dividing by zero. If I do the same process as a COMPUTE using the divided fields, it works as desired. The strange part is that if I do the division in PMF's single mapping field, it only loads zeros. I am able to do other calculations, such as subtractions in PMF and that flows through. I'm going to take this to the PMF forum, as I think this might be the end of the WebFOCUS specific part of my question.

Thank you for your help.

Cheers,

Joey

You might also be able to do it without IF's !

Since an equation internally is evaluated as a 0 or a 1 you could put that to use.


Sample below shows a COMPUTE comparable with

COMPUTE TEST/D12 = IF COUNTRY EQ 'JAPAN' THEN SALES ELSE 0;

But without an IF...


See sample:

TABLE FILE CAR
	SUM COMPUTE TEST/D12 = (COUNTRY='JAPAN') * SALES;
	BY COUNTRY
ON TABLE PCHOLD FORMAT HTML
END

A bit McGuyverish...


Greets from yet another part of the globe :-)

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

Oh, sneaky.

I think you get the award for extreme cleverness.

quote:

(COUNTRY='JAPAN') * SALES

I had no idea we could have expressions like those in WebFOCUS ... it's so C-ish! I love it!

Ah, is there no end to the wonders of FOCUS.

I think this may be what you want:

 
DEFINE FILE CAR           
A/D5.2 WITH COUNTRY = 5 ; 
B/D5.2 WITH COUNTRY = 6 ; 
C/D5.2 WITH COUNTRY = 7 ; 
D/D5.2 WITH COUNTRY = 8 ; 
MYVAL/D12.2 =                                                 
  IF A LT 0 THEN (                                            
    IF B LT 0 THEN (                                          
      IF C LT 0 THEN (                                        
        IF D LT 0 THEN ((A+1) / (B+1)) - ((C+1) / (D+1))      
        ELSE           ((A+1) / (B+1)) - ((C+1) / (D-1)) )    
      ELSE (                                                  
        IF D LT 0 THEN ((A+1) / (B+1)) - ((C-1) / (D+1))      
        ELSE           ((A+1) / (B+1)) - ((C-1) / (D-1)) ) )  
    ELSE (                                                    
      IF C LT 0 THEN(                                         
        IF D LT 0 THEN ((A+1) / (B-1)) - ((C+1) / (D+1))      
        ELSE           ((A+1) / (B-1)) - ((C+1) / (D-1)) )    
      ELSE (                                                  
        IF D LT 0 THEN ((A+1) / (B-1)) - ((C-1) / (D+1))      
        ELSE           ((A+1) / (B-1)) - ((C-1) / (D-1)) ) ) )
  ELSE (                                                        
    IF B LT 0 THEN(                                             
      IF C LT 0 THEN(                                           
        IF D LT 0 THEN ((A-1) / (B+1)) - ((C+1) / (D+1))        
        ELSE           ((A-1) / (B+1)) - ((C+1) / (D-1)) )      
      ELSE (                                                    
        IF D LT 0 THEN ((A-1) / (B+1)) - ((C-1) / (D+1))        
        ELSE           ((A-1) / (B+1)) - ((C-1) / (D-1)) ) )    
    ELSE (                                                      
      IF C LT 0 THEN(                                           
        IF D LT 0 THEN ((A-1) / (B-1)) - ((C+1) / (D+1))        
        ELSE           ((A-1) / (B-1)) - ((C+1) / (D-1)) )      
      ELSE (                                                    
        IF D LT 0 THEN ((A-1) / (B-1)) - ((C-1) / (D+1))        
        ELSE           ((A-1) / (B-1)) - ((C-1) / (D-1)) ) ) ) ;
END                  
-RUN                 
TABLE FILE CAR       
PRINT A B C D MYVAL  
IF RECORDLIMIT EQ 1  
END   

 

Change the tests to your tests, and the 'THEN' and 'ELSE' results to your computations. I used

IF(A LT 0) THEN A+1 ELSE A-1;

etc. as an example.