Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Multiple IF statements in one COMPUTE?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Multiple IF statements in one COMPUTE?
 Login/Join
 
Guru
posted
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
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
win some, lose some.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Guru
posted Hide Post
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
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
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
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Expert
posted Hide Post
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.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Guru
posted Hide Post
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
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Master
posted Hide Post
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
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
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!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 61 | Registered: November 15, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Multiple IF statements in one COMPUTE?

Copyright © 1996-2020 Information Builders