Focal Point
[CLOSED] Multiple IF statements in one COMPUTE?

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9767064616

February 09, 2012, 12:42 PM
Moogle
[CLOSED] Multiple IF statements in one COMPUTE?
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,


-WebFOCUS 8.2.01 on Windows
February 09, 2012, 02:00 PM
j.gross
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.
February 09, 2012, 02:04 PM
njsden
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 Wink



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 09, 2012, 02:05 PM
njsden
Oh man, timing! timing is always a funny thing!



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 09, 2012, 02:14 PM
j.gross
win some, lose some.


- Jack Gross
WF through 8.1.05
February 09, 2012, 02:19 PM
njsden
Big Grin



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 09, 2012, 02:56 PM
Waz
As usual, hours behind the train, and the same idea.

Frowner


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 09, 2012, 03:12 PM
njsden
You'll have to move to this side of the planet Smiler

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 09, 2012, 03:39 PM
Waz
Yes it gets a little lonely....


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 09, 2012, 04:14 PM
Moogle
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


-WebFOCUS 8.2.01 on Windows
February 09, 2012, 04:23 PM
Waz
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 09, 2012, 04:30 PM
Dan Satchell
Or...could you create a DEFINE function that you can call in your COMPUTE?

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


WebFOCUS 7.7.05
February 09, 2012, 04:44 PM
Moogle
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
February 09, 2012, 04:49 PM
Doug
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.
February 10, 2012, 10:17 AM
Moogle
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
February 14, 2012, 04:00 AM
Dave
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,


_____________________
WF: 8.0.0.9 > going 8.2.0.5
February 14, 2012, 03:24 PM
Waz
Oh, sneaky.

I think you get the award for extreme cleverness.




Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 14, 2012, 03:36 PM
njsden
quote:
(COUNTRY='JAPAN') * SALES

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
February 14, 2012, 03:46 PM
Waz
Ah, is there no end to the wonders of FOCUS.




Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 17, 2012, 06:05 PM
Edward Wolfgram
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.


IBI Development